Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008

    Unanswered: Database Design with Foreign key

    EDIT >>> I've removed all the code because my question relates more to design rather than syntax. Any guidance would be appreciated thanks!

    Lets say I start with 3 tables:
    -Tickets (stores problems)
    -Customer (clientid, name ..)
    -employee (employeeid, name)

    My goal is to link tickets to a customer OR/AND an employee. So I figure I need a 4th table. Lets call it tickettracking

    - TicketTracking

    I'm trying to wrap my head around a schema for ticket tracking. The confusing part is that, either clientid OR employee can be null. BUT both CAN'T be null at the same time.

    So I think I'm off with my schema. Any ideas would be appreciated.

    Clientid INTEGER,
    employeeid INTEGER,
    FOREIGN KEY (clientid) REFERENCES client,
    FOREIGN KEY (employeeid) REFERENCES employee,

    1 for Tickets.. (stores, the request a user submits) 1 for client, (stores clients) and 1 table for Managers(stores employees)
    how do i make a column for ticket tracking.. that will either tie a ticket to a client OR a Manager?
    Last edited by Eric the Red; 10-08-09 at 15:46.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    These statements are invalid, as far as Oracle is concerned.

    Are you sure you are using Oracle? If so, perhaps you should check the documentation (for example, there's no "autoincrement", "size" is invalid column name, there's no "double" datatype, ...).

  3. #3
    Join Date
    Oct 2009
    South Africa, the land of mystery
    The client and manager tables can be the same table, called Contact. Add a field named Contact_Type and in there specify the class of contact, i.e. Customer / Manager. Merge the tables and populate the appropriate fields only accoring to contact class. The TICKETTRACKING table then becomes: TICKET_ID, CONTACT_ID only - both FK's and combined the PK for the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts