inheritance in relational database 1

[yekta68]

hi

How i implement inheritance in tables on a relational database?
for example i have the members class that is a super class and it has only user name attribute.my child classes are:admin_users and other_users that derived from members super class.these child classes consists of password attribute
for that user.i want to know each work must be done: implement this hierarchy in one table in database or each class must has own table.

thanks a lot.

 

[jmeckley]

there are 3 ways to handle this
1. 1 table with a discriminator column
2. 1 base class User with a 1:1 FK to AdminUser and NormalUser tables
3. AdminUser table and NormalUser table. use 2 separate queries to lump them all together a "users" when needed.

If you only have a few unique columns between Admin and Normal users option 1 is your best bet. it's simple and easy to query. with this approach the subclass attributes are not required in the database.

if you want the subtype attributes to be required in the database option 2 is the way to go. it complicates queries, but an ORM should handle that for you.

option 3 is really only useful for legacy systems. you loose FK constraints and sql queries can become very complex/convoluted.

there may be other options, but these are 3 I have seen.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259