Representing specializations/generalizations (1 Viewer)

[cabusmichael]

Hello,
New to this community...glad to have stumbled on it..

I am using the "relationships" activity pane, defining relationships between tables. In my database design, I have defined some specializations..the parent table of these specializations is participating in a many-to-many relationship, so I have a third table (what Access calls a junction table, or, "for each binary M:N relationship type R, create a new relation S to represent R"). Should these specializations be defined in a relationship with this "junction" table, or with the parent table?

I'm not sure if my terminology makes sense--I have been a student, and just finished...this is the first database I am working on that is not a school project (being made for actual use ). I have done a lot of command-line, and some Access, but I often found myself working in groups, writing a lot of documentation...


Thanks for any help,

Michael

 

[georgedwilkinson]

I love this topic and I'm considering writing an article on it.

Over the years, I've found 5 ways of implementing general/specialized entities in a database. The "best" 2 (IMHO) are to 1) implement the specialized tables in separate tables and implement the "general" table with FKs from the specialized tables, making all FKs with the general table; or 2) bunch all the general and specialized stuff up into one table and use triggers or a different mechanism to enforce non-nulls.

This is not a casual topic and is quite difficult to articulate because there are no set best practices. Whatever you do, I think you're best off worrying about normalizing your design and worry less about polymorphism in a relational DBMS world.

 

[cabusmichael]

George,
Quick thank you for the reply--this project has been great because I have been able to put into practice some topics I learned in course work (ethnomethodology in action! ).

I appreciate your time--I hope to help others, too--it's awesome when something like this is available...

Kind regards,

Michael