0

I'm working on a Database in which I'm trying to deduce the best ways to apply inheritance.

So far I was having 2 subclasses from an Entity, and I asked in Extended Entity-Relationship Model to tables (subclasses) about how to implement it on relational tables.

I decided to go with Concrete Table so I created 2 tables, one for each subclass of the Entity. I encountered 2 problems:

  1. My primary keys were id int primary key autoincrement, which means the first row of each table is gonna have id = 1. So the key isn't actually unique, So when referencing it from another tables, there is no way to know which of the 2 table subclasses are we referencing (unless I add an unnecesary(?) extra column.

  2. When adding a Foreign Key that references said id, the foreign key is supposed to referece both subclasses tables, but I don't know if that is even possible.

Any ideas or opinions about how this could be done could help a lot. thanks

Community
  • 1
  • 1
Christopher Francisco
  • 15,672
  • 28
  • 94
  • 206

1 Answers1

1

It would probably make sense to have the child class tables reference the parent class, instead of the other way around. Then you can have an id column on the Entity table which is unique and foreign keys from the children to their parent instances. Presumably this will help when you want to use the data to instantiate an object in your code as well, since you should know which class you are instantiating and only care about its ancestors, not its children.

seanmk
  • 1,934
  • 15
  • 28
  • could you provide an example please? your solution seems like what I'm looking for – Christopher Francisco Jul 20 '13 at 16:25
  • Imagine 4 tables, entity, child0, child1 and grandchild. All 4 tables have id columns which are autoincrement. The child0 and child1 tables have a column named entity_id which is a foreign key to entity.id. The grandchild table has a child0_id column which is a foreign_key to child0.id. Then say you want to instantiate a Grandchild object with data from the database, getting fields from its table and all its ancestors. You can query this by just selecting the grandchild.id you need and joining the child0 and entity tables using the foreign keys. Does this help? – seanmk Jul 21 '13 at 21:47