Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type

Question

I am working on a database design and am wondering about the pros and cons of two setups. I am going to collect data about two types of entities, lets say cars and boats. The structure of the data will be the same (at least for now). There will be 10s of millions of rows. I could either:

1. create one table for boats and one for cars, or
2. create one table for both with an extra column indicating whether it's a boat or a car record

The second option would obviously mean I would need to write my procedures twice but perhaps there are good reasons for doing this, like keeping the number of rows smaller per table which might help in performance.

Which approach would perform better? Which approach is more appropriate from a design standpoint?

Answer 1

The only thing I'll add is that I find it easier to split later than combine later. Depending on what type of information you are looking at about Boats or Cars and how many points you care about and what you are doing with the data (To @JackDouglas's point about needing more specifics to give specific advice) maybe you won't need them combined, maybe you will. If you aren't sure and can design with them combined and are leaning that way, it is worth a try and testing knowing that someday you may have to split them out. Won't make refactoring easy, but IMO it would be easi*er* than trying to combine the data, if only by a little.

Answer 2

I've +1 both @gbn and @JackDouglas answers as I broadly agree with their thinking but I want to expand on a couple of points. @gbn is hinting at the most important consideration here, expanded on by @JonathanKehayias in comments.

What are you modelling and how will it be used?

If a Car and Boat are separate entities that will be acted on independently and treated differently they should be modelled separately. If however your system will be dealing with a Vehicle type, with no differentiation between Car, Boat, Truck, Motorcycle... then a Vehicle table is likely the most appropriate.

There is no right or wrong based on the information you've provided, it depends. Good odds you want a combination of both, a parent type with shared attributes with child tables for each entity.

The second option would obviously mean I would need to write my procedures twice...

Not necessarily. Code generation tools (Codesmith, Visual Studio T4) can take the strain where there is obvious repetition, particularly with CRUD.

... but perhaps there are good reasons for doing this like keeping the number of rows smaller per table which might help in performance.

Again, this will depend on how the model will be used. If you create individual tables but your query patterns necessitate accessing all the majority of the time you've lost that benefit.

Answer 3

The structure of the data will be the same

You need to make a judgement call about whether this will remain true. Some principles to bear in mind:

• Model for what you know now (ie don't try and cover conceivable eventualities).
• but, use what you know about your data and business to make some reasonable judgement calls about things that may change in the near future.
• Refactoring isn't a trivial task but neither should it be avoided at all costs.
• Especially if your model is likely to be subject to regular changes, consider isolating your tables and data with a transactional API and preventing all direct CRUD access. Schema changes then become less painful than they would otherwise be.

Without any specifics about your business or data, no more specific advice can be given.

Answer 4

A boat and a car are separate entities: this means a separate table.

There are cases where you want to maintain some kind of uniqueness of "Vehicle" where the non-shared attributes are in child tables Boat and Car of the parent Vehicle table. But in this case the simple solution is to have 2 tables.

As soon as the attributes diverge you'll have refactoring of SQL code and the client to deal with separate stored procedures etc

Edit: as mentioned in comments and other answers, we need more information.

Answer 5

create one table for both with an extra column indicating whether it's a boat or a car record

This option is only reasonable if cars and boats have the same properties and that the existance of those properties aren't going to vary based on vehicle type. If cars and boats will have different properties, then they really belong in separate tables.

As an aside, some people make modifications for Microsoft's Flight Simulator to fashion a boat simulator. In these mods, a boat is a boat-shaped airplane (look, no wings) that has a maximum altitude set to 0. I am suspecting that if you end up adding properties, you will have strange bugs when "accidentally" setting boat-specific values to things that only relate to cars (oh, the license plate column only applies to cars and should be null for boats) and vice versa.