DB design when you have 2 belongsTo relationships to 2 diffrent tables

Hi,

I'm going to make a tour ticket service for a travel agency, I have 4 kind of places that company has tour for it:

Country

Province

City (or village)

Cool places (Lake, waterfall, museum, ...)

And I have 1 table for each. Obviously any Province belongsTo a Country, any City belongsTo a Province. But any attractive place belongsTo a City (like a museum) OR belongsTo a Province (like a lake far from a city). but NOT belongsTo both in the same time.

How I should design my Mysql database for this?

Second question:

I have a table for Tours, any tour belongsTo one of these 4 table (just one). Like a 2 days tour for a lake or 3 weeks tour for a country. I don't think a polymorphic relationship is a good idea for that. Am I right?

I would be happy to hear any suggestions about my DB structure.

Thank you.

Snapey

2 years ago

Level 122

I would say a polymorphic relation would be suitable for both questions.

In your first example, the polymorphic relation could be called location and would have an id column and a model name column on the attractions table.

In the second example, the tour might also have a location relationship

I wrote an article a long time ago about a user having a one of several profile types. If you think of user as Tour and your locations as Profiles then this may be of help https://novate.co.uk/using-laravel-polymorphic-relationships-for-different-user-profiles/

1 like

Please or to participate in this conversation.