two tables needing unique USER_ID to be referenced by other tables
Date: 26 Oct 2001 08:13:41 -0700
Message-ID: <39058151.0110260713.7af53ad1_at_posting.google.com>
I have a design issue that I'm not quit sure how to handle. I need to have an EMPLOYEE_PROFILE table and GUEST_PROFILE table that both have a PK of USER_ID. USER_ID must be unique for both tables - i.e. the same USER_ID can't exist in both EMPLOYEE_PROFILE and GUEST_PROFILE. The EMPLOYEE_PROFILE has many more fields than GUEST_PROFILE, and although GUEST_PROFILE will have a couple of duplicate fields (fname, lname) as EMPLOYEE_PROFILE they can't be in the same table. I can't put the guest into the employee profile table because there is a unique column SSN that is used - and this is how user do most of their queries on employees.
There are several tables that will need to verify the existence of the USER_ID in either EMPLOYEE_PROFILE or GUEST_PROFILE. The reason I have to do this is there are a number of tables that will need guest info, but there quite a few more that won't. For example, an employee can bring a guest(s) to an event that the guest needs to register for and that has activities that the guest can sign up for.
Does anyone have any thoughts on the best way to design something like this? I came up with something that I'm not sure if it would work - and don't really like the design, but it was all I could come up with. Please let me know if you have any ideas, or if not do you think what I've come up will work.
Thanks,
Lisa
- MY DESPERATE IDEA! ** What if I had a USER_PROFILE table that just had the columns USER_ID (auto-generated) and EMPLOYEE - this column being a flag Y/N. If the flag were set to Y then a trigger would insert a row into the EMPLOYEE_PROFILE with the newly generated USER_ID or vise versa if the flag were set to N. I suppose the user interface would need to accommodate presenting them with different columns (there would be more for employees vs. guest) based on the flag setting.
Do you think this would work? If so, one other question I have is how would it work if there were no user interface to prompt them for the additional info (SSN, FNAME, etc.) that would need to be put into the EMPLOYEE_PROFILE or GUEST_PROFILE tables? What would happen if I did a manual insert into the USER_PROFILE table - would the trigger add a row to either EMPLOYEE_PROFILE or GUEST_PROFILE with just the newly generated USER_ID? That wouldn't be good... any suggestions on how to deal with that?
- CURRENT TABLE STRUCTURE **