Onetomany with parent database design

Question

Below is a database design which represents my problem(it is not my actual database design). For each city I need to know which restaurants, bars and hotels are available. I think the two designs speak for itself, but:

First design: create one-to-many relations between city and restaurants, bars and hotels.

Second design: only create an one-to-many relation between city and place.

Which design would be best practice? The second design has less relations, but would I be able to get all the restaurants, bars and hotels for a city and there own data (property_x/y/z)?

Update: this question is going wrong, maybe my fault for not being clear.

• the restaurant/bar/hotel classes are subclasses of "place" (in both designs).
• the restaurant/bar/hotel classes must have the parent "place"
• the restaurant/bar/hotel classes have there own specific data (property_X/Y/X)

Answer 1

Good design first

Your data, and the readability/understandability of your SQL and ERD, are the most important factors to consider. For the purpose of readability:

• Put city_id into place. Why: Places are in cities. A hotel is not a place that just happens to be in a city by virtue of being a hotel.

Other design points to consider are how this structure will be extended in the future. Let's compare adding a new subtype:

• In design one, you need to add a new table, relationship to 'place' and a relationship to city
• In design two, you simply add a new table and relationship to 'place'.

I'd again go with the second design.

Performance second

Now, I'm guessing, but the reason for putting city_id in the subtype is probably that you anticipate that it's more efficient or faster in some specific use cases and this may be a very good reason to ignore readability/understandability. However, until you measure performance on the actual hardware you'll deploy on, you don't know:

• Which design is faster
• Whether the difference in performance would actually degrade the overall system
• Whether other optimization approaches (tuning SQL or database parameters) is actually a better way to handle it.

I would argue that design one is an attempt to physically model the database on an ERD, which is a bad practice.

Premature optimization is the root of a lot of evil in SW Engineering.

Subtype approaches

There are two solutions to implementing subtypes on an ERD:

1. A common-properties table, and one table per subtype, (this is your second model)
2. A single table with additional columns for subtype properties.

In the single-table approach, you would have:

• A subtype column, TYPE INT NOT NULL. This specifies whether the row is a restaurant, bar or hotel
• Extra columns property_X, property_Y and property_Z on place.

Here is a quick table of pros and cons:

Disadvantages of a single-table approach:

• The extension columns (X, Y, Z) cannot be NOT NULL on a single 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 as you add additional subtypes. You may hit the max. number of columns on some databases. This can make this design quite wasteful.
• To query a list of a specific subtype, you have to filter using a WHERE TYPE = ? clause, whereas the table-per-subtype is a much more natural `FROM HOTEL INNER JOIN PLACE ON HOTEL.PLACE_ID = PLACE.ID"
• IMHO, mapping into classes in an object-oriented languages is harder and less obvious. Consider avoiding if this DB is going to be mapped by Hibernate, Entity Beans or similar

Advantages of a single-table approach:

• By consolidating into a single table, there are no joins, so queries and CRUD operations are more efficient (but is this small difference going to cause problems?)
• Queries for different types are parameterized (WHERE TYPE = ?) and therefore more controllable in code rather than in the SQL itself (FROM PLACE INNER JOIN HOTEL ON PLACE.ID = HOTEL.PLACE_ID).

There is no best design, you have to pick based on the type of SQL and CRUD operations you are doing most frequently, and possibly on performance (but see above for a general warning).

Advice

All things being equal, I would advise the default option is your second design. But, if you have an overriding concern such as those I listed above, do choose another implementation. But don't optimize prematurely.