Re: two tables needing unique USER_ID to be referenced by other tables

> need EMPLOYEE_PROFILE and GUEST_PROFILE both have
> a PK of USER_ID. USER_ID must be unique for both tables
> 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.
> unique column SSN. 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.

In addition, assume that Activities can be repeated at different Events, and the Activity's Start/End times can be different from the default. Below are the tables needed for a normalized solution in a relational db.

T_Person

T_Employee
  PersonID

T_Event

T_Activity
  Start
  End

T_EventActivityMap
  EventID
  ActivityID

T_EventActivity_Time
  EventAvtivityMapID
  Start
  End

T_EventActivity_Emp
  EventActivityMapID
  EmployeeID

T_EventActivity_Emp_Guest
  EventActivity_Emp_ID
  GuestID (Alias for PersonID)

In a relational database, a normalized solution is fairly complex and probably impractical. In an object-oriented database, like XDb, a normalized solution is practical. Why would one want a normalized solution? See http://www.xdb1.com/Normalization.asp

Below is a solution which can be downloaded from http://www.xdb1.com/Example/Ex045.asp

Note: In the object-oriented solution shown below "." is a property, "->" indicates a reference. "[]" indicates object's class. Each instance can have fewer or more properties than described in its class. Each property can have multiple values as indicated by its children. Each property can have multiple sub properties. Each object has a unique id.

Event
 .Activity
   ->Activity1

     .Start (optional)
     .End   (optional)
     .Empolyee
       ->Employee1
           .Guest
              ->Guest1
              ->GuestN
       ->EmployeeN

   ->ActivityN

Activity
 .Start
 .End

Employee[Person]
 .Emp#
 .OtherProperties

Person {alias Guest}
 .SSN#
 .OtherProperties

Notice XDb's simplicity in representing the following tables: T_EventActivityMap, T_EventActivity_Time, T_EventActivity_Emp and T_EventActivity_Emp_Guest. In a relational database, the information for a single entity is spreadout over multiple tables and one typically needs forms/views to make it comprehensible. In contrast, XDb presents most of the information for a single entity without creating forms/views, however custom interfaces can be created using VB, Delphi, C++, or ASP.

XDb is an object-oriented database that can manage complex/variable data structures. Download your free copy (260 KB) from http://www.xdb1.com Received on Sun Oct 28 2001 - 01:39:27 CEST