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

database-designsql server

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:

create one table for boats and one for cars, or
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?

Best Answer

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.

Related Solutions

Where does a type specific field go

What you are proposing is an entity subtyping approach. This would be one common solution to your design problem. Another would be Entity-Attribute-Value (EAV).

Type "subtype" or "EAV" into the search box in the top right hand corner of the page and you'll see many questions describing and discussing the relative pitfalls and merits of each.

Whether subtyping (or, alternatively EAV) is a "really bad design" in your case depends on exactly how many different unique features and manufacturers you need to account for and perhaps where you stand philosophically (some people insist that EAV is always evil-for example).

To answer your specific question about enforcing a match between brands and their unique attributes, the only way to do this is with procedural code. Depending on your DBMS you might be able to do this with triggers. There isn't a way to to it declaratively.

How to query supertypes to get subtype data

Unless you use dynamic SQL to build the query (which adds no value anyway) then SQL is a "fixed column" language. That is, you'd have to send all columns you need. This example is bad in that you have all columns but you get the idea.

SELECT
   V.*,
   C.*,
   B.*
FROM
   Vehicle V
   LEFT JOIN Car C  ON V.VEH_ID = C.CAR_ID
   LEFT JOIN Boat B ON V.VEH_ID = B.BOAT_ID

Another option is to hide the VEHICLE-CAR join behind a view "CARS" and VEHICLE-BOAT join behind another view "BOATS". However, you can't UNION them as the have different columns and types

Basically, you don't query supertypes/subtypes like this because each subtype gives a different resultset.

Best Answer

Related Solutions

Where does a type specific field go

How to query supertypes to get subtype data

Related Question