0

This has to be a common question, but I can't find good advice.

If I have multiple roles, each reflected in their own table, but I have common elements that could be distilled into one table, and... the common elements could change, how can I find all the common elements of a given role?

For example, I could have an address table for the common elements of an education database. I could have teachers, students, suppliers, general contractors as specific roles, each with their own table (because they have fields specific to their roles). Do I include a teacherId, studentId, suppliers and generalContractorId, all four role foreign keys in my single Address table? I realize, if I only wanted to store the current address, this would not be needed, but if I want all addresses for a given role, including previous addresses for an individual, I need something more.

Even if I create a table between the roles and the addresses, I don't know how to populate it in terms of a primary index (e.g. "t" + incremental index for teachers, "s" + incremental index for students, etc.)

Or do I simply have a role type (enum) concept in the address table that "tells" me this row is for a given type of role?

Also... I would like a solution that is in 3NF if possible. Would someone please confirm that the other solution in stackoverflow complies with this?

Sorry for the language used; new to databases.

NAs
  • 89
  • 6
  • 2
    Looks like this will help me: http://stackoverflow.com/questions/2862918/common-one-to-many-table-for-multiple-entities – NAs Dec 05 '15 at 17:08
  • But I am not convinced that the solution I found is in 3NF. The most selected solution implies that I will have x-fields in a separate table that will potentially be null. – NAs Dec 05 '15 at 17:50

0 Answers0