I have a design issue that I'm not quite 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 **
EMPLOYEE_PROFILE
-----------------
USER_ID
SSN
FNAME
MNAME_INITIAL
LNAME
SEGMENT_ID
REPTYPE_ID
REGION_ID
AREA_ID
DEPT_ID
TITLE_ID
MGR_SSN
ADDRESS1
ADDRESS2
ADDRESS3
CITY
STATE_CODE
ZIP
COUNTRY_CODE
PROVINCE
OFFICE_ADDRESS1
OFFICE_ADDRESS2
OFFICE_ADDRESS3
OFFICE_CITY
OFFICE_STATE_ID
PHONE_HOME
PHONE_OFFICE
PHONE_MOBILE
FAX
PAGER
EMAIL
CREATE_DATE
MODIFY_DATE
TERMINATE_DATE

GUEST_PROFILE
-------------
USER_ID
HOST_SSN
FNAME
MNAME_INITIAL
LNAME