The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
SQL total overlapping specialization
Richy
Registered User regular
Registered User regular
I've been racking my brains over this for a while: say I've got a general class that specializes in several specific subclasses. Specialization is mandatory for the general class, but the specialized classes are not mutually exclusive and an instance can belong to multiple classes at once. How do you code that in SQL?
For example: Say you have a database for a company with a general table "Employee" that contains the attributes "employeeID" (primary key) and general attributes (name, address, etc.). And you have specialized tables for the jobs in the company like "Engineer", "Secretary", "Manager", etc., each inheriting "employeeID" as a foreign key and with their own specific attributes unique to their job. An employee must have at least one job in the company, but can wear many hats and thus hold several jobs at once.
I can make each individual table easily enough, but where I get stumped is with the constraints. I just can't figure out how to make it so that an Employee tuple must exist in one or more specialized tables - every solution I come up with using triggers, procedures, constraints, etc., either allows an Employee tuple with no specialized tuples or forces a single specialized tuple. I've googled the problem in every phrasing I can think of, but come up empty.
Anyone here have any ideas?
For example: Say you have a database for a company with a general table "Employee" that contains the attributes "employeeID" (primary key) and general attributes (name, address, etc.). And you have specialized tables for the jobs in the company like "Engineer", "Secretary", "Manager", etc., each inheriting "employeeID" as a foreign key and with their own specific attributes unique to their job. An employee must have at least one job in the company, but can wear many hats and thus hold several jobs at once.
I can make each individual table easily enough, but where I get stumped is with the constraints. I just can't figure out how to make it so that an Employee tuple must exist in one or more specialized tables - every solution I come up with using triggers, procedures, constraints, etc., either allows an Employee tuple with no specialized tuples or forces a single specialized tuple. I've googled the problem in every phrasing I can think of, but come up empty.
Anyone here have any ideas?
0
Posts
But how do you enforce the requirement that an employee must have at least one job in this setup?
I don't think there's any real in built syntax-y way of doing this simply, that's the realm of business logic where you start getting into triggers/functions/stored procedures like you were talking about.
You could have a trigger autoinsert when an employee is added that gives them a job role of unspecified, or you could just have a function that requires at least one job role ID (or many) and errors out if one isn't provided.
I could be wrong about that though. I usually put this kind of enforcement into my code itself, I hate business logic in my databases.
I broadly agree with bowen but some commentary:
First off, I agree it would be better for this kind of data validation to be done at a higher level than the database. Database-level validation (SQL constraints, DRI, etc) are good and IMO necessary for simpler logic but more complicated logic can get finicky (as you're discovering).
Secondly...
"And you have specialized tables for the jobs in the company like "Engineer", "Secretary", "Manager", etc., each inheriting "employeeID" as a foreign key and with their own specific attributes unique to their job."
This is kind of a weird way to do it. Is there a specific reason why you need each job to have a separate table? Here's how I'd consider doing it. (This will also conform better to common data normalization & database design principles.)
Imagine you have three relevant tables.
Table 1: Employees
Table 2: Roles
Table 3: role Assignments
The employee table might look like this:
Roles:
Assignments: (The roleID could then be defined as foreign keys.)
If you want to answer "what roles does Jean-Luc Picard have?" then you'd do something like "SELECT FROM [Assignments] WHERE [employeeID]='00000001'"
"Who has the role of Doctor?" then it's "SELECT FROM [Assignments] WHERE [roleID]='00000003'"
the "no true scotch man" fallacy.
I'm actually creating a web app that has a hierarchy similar to yours. Users have specific roles within groups they're assigned to, but it occurred to me that a person could have their roles temporarily rescinded but I'd still want them to have an account to access the app. I don't want to have to re-add a user every time they come back from a hiatus, so I didn't implement a requirement for them to have a role (or even a group).
You could always run a report of users that don't have jobs assigned to them and figure out why that's the case.
To be honest, it's a "prove it can be done" kind of thing. Otherwise, as several people suggested, I could have gone with a business logic solution and saved myself some headaches.
I figured out one solution I think. I added a vector of bits in the Employees table with one bit per job, with a table constraint stating that at least one bit must be 1 (or more precisely, that the sum of bits must be greater than 0). Then I added triggers so that after each time a bit changes to 1 (or 0) a tuple is added to (or deleted from) the corresponding job table, and triggers that prevent the deletion of tuples from jobs tables if the corresponding bit is 1. I think that solves it.
Essentially bit masking like you'd see with enums? Definitely an idea that could work.
Employee table would/must point to a role, and any other roles would then get pointed to from other role tables.
And then someone deletes the middle record of the list and it doesn't work anymore.
I like the previously mentioned solutions much better.