1

Let's say I have a database in which one entity (i.e. table) inherits from another one, for example:

  • Table 1, named person: (name,surname)
  • Table 2, named car_owner

In this case, car_owner inherits from person, i.e. a car-owner IS a person. I'm now in a point where I have to decide whether I should:

  1. create the table car_owner, even though it has no extra columns except the ones in person, although in the future this might change => doing this results in car_owner = table with columns (id,person_id), where person_id is FK to person

or

  1. leave only the person table for now and only do (1) when/if extra information regarding a car-owner will appear => note that if I do this FKs to a car-owner from other tables would actually be FKs to the person table

The tables I'm dealing with have different names and semantics and the choice between (1) and (2) is not clear, because the need for extra columns in car_owner might never pop-up.

Conceptually, (1) seems to be the right choice, but I guess what I'm asking is if there are any serious issues I might run into later if I instead resort to (2)

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
Zuzu Corneliu
  • 1,594
  • 2
  • 15
  • 27
  • car-owner IS A ROLE PLAYED BY person. A person can play many roles (at the same time). Also, companies / organizations can own cars ;) – Neil McGuigan Apr 13 '15 at 22:44
  • @NeilMcGuigan I can't seem to draw a conclusion relevant to my question from your comment, care to rephrase? – Zuzu Corneliu Apr 13 '15 at 22:48
  • Read up on some ready to use patterns: http://dba.stackexchange.com/questions/12991/ready-to-use-database-models-example/23831#23831 You are looking for Party Model (with Roles) – Neil McGuigan Apr 13 '15 at 23:01

1 Answers1

2

I would suggest that option 1 is the better answer. While it creates more work to join the tables for queries, it is neater to put "optional" data in it's own table. And if more types of persons are required (pedestrian, car_driver, car_passenger) they can be accommodated with more child tables. You can always use a view to make them look like one table.

BTW for databases, we say Parent and Child, not "inherets".

To answer the part about problems/consequences of option 2 - well, none too serious. This is a database, so you can always re-arrange things later, but there will be a price to pay in rewriting queries and code if you restructure tables. Why I don't like Option 2 is because it can lead to extra tables not re-using the person part. If it looks like that table is for car_owners, I might make an entirely new table for car_passengers with duplication of all the person columns. In short, nothing too tragic should happen with either approach, they are each preferable for different reasons and the drawbacks are mainly inconvenience and potential future messiness.

Turophile
  • 3,367
  • 1
  • 13
  • 21
  • thanks, valid points, but too general - I'm still not convinced that choosing (2) won't lead to serious issues later - by serious I mean having to put a lot of effort into adjusting tables/queries/code.. – Zuzu Corneliu Apr 13 '15 at 23:01
  • also...I don't quite agree with the car_passengers example - I think its clear from the table name - "person" - that rows in it represent persons :) – Zuzu Corneliu Apr 13 '15 at 23:05
  • Let's talk about your problem more. You said that `car_owner` has the same columns as `person`. If you really have the exact same columns (presumably name, age, address etc) then I would go with one table with the addition of a `person_type` column to flag this person as a car owner. HOWEVER, as soon as you need another column specific to `car_owner` then you should probably make another table. What you definitely *should not do* is put name, age, address columns in both tables. The rule is *Only store each type of information in one place*. – Turophile Apr 13 '15 at 23:11
  • adding a flag (a 'role') in the person table was never an option and neither was duplicating data/columns from the person table...what I'm asking here is if there are situations where choosing option (2) would imply having to put substantial effort in adjusting stuff later, when the need for a 'car-owner' specific column would appear. I'm asking what potential of messing things up in the future has choice (2) => specifically.. :) I understand that your opinion is that it has little potential to do so, but I'm not quite convinced myself – Zuzu Corneliu Apr 13 '15 at 23:27