Onetomany with parent database design
Below is the database design that presents my problem (this is not my actual database design). For each city, I need to know what restaurants, bars and hotels are available. I think the two projects speak for themselves, but:
First design: create a one-to-many relationship between city and restaurants, bars and hotels.
Second construct: Create a one-to-many relationship between city and place.
What's the best design? The second project has less relationships, but can I get all the restaurants, bars and hotels for the city and there own data (property_x / y / z)?
Update: This question goes wrong, maybe my mistake for not understanding.
- restaurant / bar / hotel classes are subclasses of "place" (in both designs).
- The restaurant / bar / hotel classes must have a parent "seat"
- Restaurant / bar / hotel classes have their own specific data (property_X / Y / X)
source to share
Good design first
Your data and the readability / comprehensibility of your SQL and ERD are the most important factors to consider. For readability:
- Place
city_id
inplace
. Why: Places in cities. A hotel is not a place that happens to be in the city thanks to the hotel.
Other design aspects to consider is how this framework will be extended in the future. Let's compare adding a new subtype:
- In design, you need to add a new table, a relation to "place" and a relation to
city
- In the second design, you simply add a new table and relation to "place".
I would go again with a second design.
Second performance
Now, I'm guessing, but the reason for placing city_id
in a subtype is probably because you expect it to be more efficient or faster in some specific use cases, and that might be a very good reason to ignore readability / understandability.However, until you evaluate performance on the very hardware you will be deploying to, you don't know:
- Which design is faster
- Whether the difference in performance will actually degrade the overall system.
- Is another optimization approach optimized (tuning SQL or database parameters).
I would say that design is trying to physically model a database in an ERD, which is bad practice.
Premature optimization is the root of great evil in SW Engineering.
The subtype is approaching
There are two options for implementing subtypes in ERD:
- General properties table and one table for each subtype (this is your second model)
- A separate table with additional columns for subtype properties.
In a single table approach, you should:
- Subtype column
TYPE INT NOT NULL
. This indicates whether the string is a restaurant, bar, or hotel. - Additional columns
property_X
,property_Y
andproperty_Z
onplace
.
Here's a quick table of the pros and cons:
Disadvantages of the single table approach:
- Extension columns (X, Y, Z) cannot be NOT NULL for one table approach. You can implement row-level constraints, but you lose the simplicity and visibility of a simple NOT NULL
- The single table is very wide and sparse, especially when adding additional subtypes. You can reach the maximum. the number of columns in some databases. This can make this project quite wasteful.
- To query for a list of a specific subtype, you need to filter with a clause
WHERE TYPE = ?
, whereas table-sub-subtype is much more natural `FROM HOTEL INNER JOIN PLACE ON HOTEL.PLACE_ID = PLACE.ID" - IMHO, mapping to classes in object-oriented languages is more complicated and less obvious. Consider avoiding if this DB is rendered by Hibernate, Entity Beans or similar
Advantages of the single table approach:
- By joining into a single table, there are no joins, so queries and CRUD operations are more efficient (but can this small difference cause problems?)
- Queries for different types are parameterized (
WHERE TYPE = ?
) and therefore more manageable in code rather than in SQL (FROM PLACE INNER JOIN HOTEL ON PLACE.ID = HOTEL.PLACE_ID
) itself.
There is no better design, you have to choose based on the type of SQL and CRUD operations you do the most, and possibly performance (but see above for a general warning).
Advice
All things being equal, I would recommend the default option - this is your second design. But, if you have an underlying problem like the ones listed above, choose a different implementation. But don't optimize prematurely.
source to share
Both of them and none of them at all.
If I need to select one, I would keep the second, due to the number of foreign keys and indexes that needed to be created after.
But the best thing would be: create a table with all types of places (bars, restaurants, etc.) and assign each row a column with a value of the type of place (use COMPRESS with the types expected in the column). This will improve the performance and readability of the structure, and will also be easier to maintain.
Hope this helps. :-)
source to share
you are not showing alternate columns in any of the lookup tables. I think you shouldn't split the type data into table names like "bar", "restaurant", etc. - these must be types within the table of places.
I think you should have a table of addresses, one of which is a city. then each place has an address and you can easily group the city as needed. (or state or postal code or country, etc.)
source to share
I think the second is the best option. In the first design, there is the potential for data errors, since one location can be assigned to a specific restaurant (or any other type) in one city (for example, A) and at the same time can be assigned to another restaurant in another city (for example, B). In the second project, a place is always tied to a specific city.
source to share
First:
Both projects can provide you with all the relevant data.
Second:
If all extending classes are going to implement location (which seems obvious to your implementation), it would be better to include it as part of the parent. This will suggest option 2.
Thingy:
The point is that even rigidly you can find out the type of each specific LOCATION, it is easier to just know that the type (BABY) is always a place (PARENT). You can think about this while you visualize the result — option 2. With that in mind, I recommend the first approach.
Note:
The former doesn't have a relationship anymore, he just breaks it up.
source to share
If a bar, restaurant and hotel have different sets of attributes, then they are different entities and should be represented by three different tables. But why do you need a place in the table? My advice is to drop it and have 3 tables for your 3 objects and that's it.
In code, collecting common attributes into the parent class is more organized and efficient than repeating them in each child class - of course. But as spatiran says in the comments, database design is not OOP-like. You will, of course, keep the repetition of the column names by inserting the common place attributes into the place table. But it will also add complexity: - you need to join this table when you want to refer to a bar, restaurant or hotel - you need to insert two tables when you want to add a new bar, restaurant or hotel - you need to update two tables when. .. etc.
With 3 tables without seat table, ALSO PROBABLY, the most performance-optimized design. But that's not where I come from. I mean simple and simple database design where one object means one row in one table. There is no "is-a" relationship in a relational database. The external key relationship is "is-a". Ok, there are exceptions, I'm sure, but your case is not exceptional.
source to share