Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Hey olle, I reworded your question to better fit your intention. Does it look better to you? –  Nick Chammas Nov 15 '11 at 20:58

5 Answers 5

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer
1  
I don't think it is quite as clear cut as you make it here. I've worked on databases where everything was a separate entity and therefore a separate table and they are a nightmare for performance, require dozens of JOINs and additional UNION operations to get the most basic information out. Without understanding the data and how it is used you can't really make a schema design decision. –  Jonathan Kehayias Nov 15 '11 at 14:35
    
@Jonathan Kehayias: true, and I've worked on systems where 50 types were folded into one table: but there was a similarity of entity. Boat and Cars don't generally have this overlap (even though they are examples). –  gbn Nov 15 '11 at 14:42

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.