Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Unanswered: How do I create a one-to-one relationship, and do I need to?

    In this database I'm creating I have a customer and an employee table. Instead of repeating information from both tables, ie an employee can be a customer and a customer can be an employee I wanted to have a separate employee table with just the hire date, employeeID and customer ID to link them together. The customerID is an autonumber field but instead of some random number, for employees it's significantly easier for employees to pick their own ID for use in logging in and using the POS, work order etc. As such I need the employeeId to be the primary key. If I link the customerID from one table to the employeeID in the employee table, then the numbers need to be the same, right? How can I create a one to one relationship and yet still be able to create the employeeid, and use it as foreign key in other tables as needed? I have attached a picture of the table as it's currently set up. If anything is unclear please let me know and I can elaborate more. Thanks!
    Attached Thumbnails Attached Thumbnails Relationship.JPG  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It sounds like you have stumbled onto your own version of a modelling technique called sub-type super-type.
    Have a google at these - see if it is a more concrete idea of what you want. In essence you would have a person table (the super type) and an employee and a customer table (the two sub type tables). All with one-to-one relationships.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2010
    Posts
    6
    Thanks, I think you're correct. The situation I want to resolve if it's possible is to allow the employee to choose their own ID to use for POS, Work Order, Purchase Orders etc. Is there a way to create a super/subtype where the EmployeeID can be different while still being the PK?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I assume currently the IDs are autonumber columns, and you want to be able to have the user chose their own ID instead of have one assigned? A bit like choosing your own username on this forum.

    Is this correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2010
    Posts
    6
    Exactly! I can't figure out how to make that work. Ideally I'd like to be able to either choose the ID when entering as a customer if wanted, or change it after a customer becomes an employee as it sometimes does. Of course sometimes the Employee has never shopped here, but then will start to shop here, so it's helpful to be able to have a great deal of arbitrary control over the value of the personID

Posting Permissions

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