SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs down DBDesigner 4 and Foreign Keys

    I am not sure if this is a bug, or if this is how the database should be designed. But from my point of view it does not make any sense.

    On the picture below you can see that I am linking several tables together. But the modeling tool (DBDesigner 4) adds extra fields that I don't need into business_location and business_event tables. It seems to propogate foreign keys from other tables. The problem is that I cannot delete them. It's all automatic.

    For example, table business_location should have only 3 keys: business_location_id, location_id and business_id. Businesses have multiple locations and the table should just join them together. I don't need to have business_type and business_slug in there at all...

    Is this the proper way of linking tables, or is this a bug?


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    if businesses have multiple locations (i.e. the business_location table is a child of the business table) and if the PK of business is the composite {business_id, business_type_id, business_slug}, then business_location must have those three columns as a composite FK to the business table

    it's quite simple, really, and i'm afraid in this case the tool is correct

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh damn. That was the composite thing. The tool had put the business_slug into the same group with the PK... Once I removed it - it was fine

    Thanks... I've been breaking my brain for a while on this one... I was just gonna edit it out manually after the db design is done..


Bookmarks

Posting Permissions

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