Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Supertype Help Please

    I need some help please. Party is the supertype to person, dealer and customer. I am having a difficult time modelling the customer and dealer tables.

    This system can have many dealers and a dealer can have many customers. What I need is for every dealer to keep a sequential list of its customer numbers. Sample data might look like this.

    Code:
    dealer_id     customer_id
    1               1
    1               2
    1               3
    2               1
    2               2
    2               3
    Since a subtype cannot contain any other PK other than the PK from the supertype I thought about adding attributes such as customer_id in the customer table and dealer_id in the dealer table and making them alternate keys. I could then link to the dealer_customer table using the AKs.

    I tried this but it does not give me my sample data above because the db is enforcing the constraint on the customer_id by not allowing me to duplicate.

    Is there another way to do this?

    Thanks.

    Frank
    Attached Thumbnails Attached Thumbnails export.bmp  
    Last edited by Frunkie; 09-21-08 at 10:20.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    On the odd chance that a customer is fully dependant on dealer (meaning that you can't have a customer without a dealer, that the dealer is in fact an identifying characteristic of the customer), then make the customer PK a composite of dealerId and customerId.

    If the customer is an independant entity (meaning that a customer can exist without a dealer, and that a customer can be transferred from one dealer to another), then the only way I know to do what you've described is to create a many-to-many linking table that joins dealers to their customers and put the dealer-relative id in that linking table. This still won't cope with deletions of the link between dealer and customer (which would leave "holes" in your numbering), but it is the closest that I can get.

    -PatP

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks for the explanation Pat. I have taken your first approach which still left holes in the customer number. I added another attribute for the customer_no in the customer table which works fine. Whether it is correct or not, I don't know.

    Frank

Posting Permissions

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