Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    supertype - subtype

    hello db gurus! :-)
    got a problem, that i cannot solve for three days already

    1. there are 2 types of owners (person and factory)

    2. owner can be only a person or factory (xor in other words)

    3. owner has many relationships with other entities (for note)

    4. person can be a doctor, specialist and so on with the corresponding properties

    so the question is how to implement this into db design.. which tables to create

    my idea was

    owner(pk: owner_id)
    person(pk: person_id, fk: owner_id)
    factory(pk: factory_id, fk: owner_id)

    the BUG is that first i MUST create record in table owner and then copy it into person or factory table depending on owner type with that all owner_id MUST unque.

    to place factory and person into one table is not very good design i guess

    if to divide them into 2 tables

    person(pk: owner_id)
    factory(pk: owner_id)

    then i have to find a way to keep owner_id unique

    what i should do? help me please

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    reading over your post a few times, i was having trouble understanding where the problem is, until it suddenly struck me -- you're thinking of using an autonumber/identity type primary key, right?

    you're right, combining person and factory is probably not a good idea

    just go with your initial design, however, with a small change --

    owner(pk: owner_id)
    person(pk/fk: owner_id)
    factory(pk/fk: owner_id)

    yes, you still must first create the owner row, then use the autonumber/identity key value to create the related row in either person or factory

    that's not a problem, that's a feature

    but in those tables, it is not an autonumber/identity
    Last edited by r937; 09-27-04 at 06:52. | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    brilliant! thanks a lot!

Posting Permissions

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