Results 1 to 10 of 10

Thread: categories

  1. #1
    Join Date
    May 2011
    Posts
    7

    categories

    Hello,

    How categories are mapped to physical design, an example is that, the car owner can be a bank or person i.e. How owner table should look like in the physical design below. This example I took from fundamentals of database management system, I have one design in my mind using generalization concept i.e the person and the bank are a sub type of owner and I can map these relations using 1-1 keys. But I need another design because I have tables with data and I need a lot of effort to do the first design.



    band (bank_id, banke_name ...)
    person(ssn, first_name,....)
    owner (id integer, )
    car (owener_id, car_name, ....)

    Regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds to me like the generalization concept (supertype/subtype) is exactly what you need

    can you explain in some more detail why you can't use it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    7
    Quote Originally Posted by r937 View Post
    sounds to me like the generalization concept (supertype/subtype) is exactly what you need

    can you explain in some more detail why you can't use it?
    Simply put, In the generalization concept you need to make sure that the keys are disjoint in the sub tables and I have two tables already populated and each table has many relationships with other tables in the schema. if I want to change the keys I need to write script to test the relation because in the existing design the cascade options are set to no action.

    i.e
    in sub table 1 i have ids 1 .....n
    in sub table 2 I have ids 1.....n

    if I want to do generalization I need to change the domain of the keys for one table such that
    subtable 2 ids in n+1 .....m and then propegate the changes manually becasue I have 'on update no action' in tables which reference these tables

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i understand

    so, what are you trying to add to this existing database? and why?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2011
    Posts
    7
    I have two entities have the same relationship to another entity. the proper solution would be generalization and map the relation on the parent level. However, I need to see if there another solution.

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    The easiest way without going back and making an entity id for all entities, so that there are no duplicates would be to add the type to the owner table.

    owner (id integer, idtype char(1))

    You would not be able to define a foreign key to both of your parent tables, it would have to be application controlled RI or trigger controlled(a check to ensure the ID exists in one of the two parents).

    Dave Nance

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Would something like this help?
    Code:
    CREATE TABLE bank (
       bankId		INT
       PRIMARY KEY (bankId)
    ,  name			VARCHAR(50)
    --  other stuff as needed
    )
    GO
    
    CREATE TABLE person (
       personId     INT
       PRIMARY KEY (personId)
    ,  name         VARCHAR(50)
    --  other columns as needed
    )
    GO
    
    CREATE TABLE car (
       carId        INT    NOT NULL
    ,  bankId       INT    NULL
       FOREIGN KEY (bankId)
          REFERENCES bank (bankId)
    ,  personId		INT    NULL
       FOREIGN KEY (personId)
          REFERENCES person (personId)
       CONSTRAINT OnlyOne CHECK
          (
             (bankId IS NOT NULL and personId IS NULL)
          OR (bankId IS NULL and personId IS NOT NULL)
          )
    )
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    Would something like this help?
    quite likely

    aside: what is this mysterious "GO" of which you speak?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    "GO" is a convenient way to limit the chaos within the tool at hand... It separates SQL statements into practical groups so that the interpreter knows where the coder thinks a logical division is, so that if/when things run amok it is easier to "put things back on the rails" because then the interpreter knows where a logical "chunk" starts.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    "GO" is a convenient way to limit the chaos within the tool at hand...
    you must've either overlooked or ignored the smiley in my post

    this thread is in the Database Concepts & Design forum, so proprietary, non-standard SQL should be avoided

    you sycophantic microsoft fanboy, you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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