Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Join Date
    Jul 2005
    Posts
    5

    One field refers to fk field in more than one fk table

    I am looking for a best-practices design case for the following:

    I am working on a web-app's (PHP) back-end database (MySQL). This db already contains tables named SERVER and WORKSTATION. Each table has an autonumber int pk field called ID.

    It occurs to me that one day that, as I added new types of inventory to this db, I will be creating new tables (e.g. PRINTER). This leads me to think about adding a table called INVENTORY. INVENTORY will also have a unique pk ID. It will also have an int field called INV_ID which store the ID from the SERVER, or WORKSTATION (or PRINTER, or whatever) table. It will have a fieldcalled INV_TYPE, which will indicate which table this ID comes from (SERVER, or WORKSTATION, or PRINTER, or whatever).

    While each of these tables has fields in common (Name, Location, etc.), they store a lot of information specific to the type of item. BUT... I want to link them to the same table. I could then move all the common fields to the INVENTORY table.

    My question is: is this good or bad design? Does the fact that the fk field in INVENTORY holds an ID that could be the foreign key to one of a number of tables denormalize the database? Is the fact that the logic will occur on the web app side (which table to link to) incorrect? Is it required that a database be totally self-contained and self-describing (i.e. there would be no way from examining the schema to determine the relationship of INVENTORY to the other tables)

    Any thoughts or suggestions are appreciated.

    Thanks,

    - Jay
    Last edited by jfugiel; 05-29-07 at 22:50. Reason: Bad Title

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by jfugiel
    My question is: is this good or bad design?
    Sounds OK so far.
    Quote Originally Posted by jfugiel
    Does the fact that the fk field in INVENTORY holds an ID that could be the foreign key to one of a number of tables denormalize the database?
    No. The primary key of the INVENTORY table can be the foreign key in as many tables as you would like.
    Quote Originally Posted by jfugiel
    Is the fact that the logic will occur on the web app side (which table to link to) incorrect?
    In my opinion, yes. What makes you think this has to be on the application side?
    Quote Originally Posted by jfugiel
    Is it required that a database be totally self-contained and self-describing (i.e. there would be no way from examining the schema to determine the relationship of INVENTORY to the other tables)
    Maybe I misunderstand. Why cannot you not enforce these relationships within the database schema?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    One Field to Rule them all
    One Field to Find them.
    One Field to bring them all,
    and in the darkness blind(man) them...
    with appologies to Dr. Tolkein, who MUST be spinning about now.

    Got to agree with blindman that it's not a good idea to attempt to place RI in the application layer. Put it in the database, close to the data, where it belongs. That way, even if you screw up the front end, or when you're importing data, or when someone else who DOESN'T know the RI rules for the database writes another app to poke some data into the database, you won't lose RI.
    Last edited by loquin; 05-30-07 at 03:44.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    Quote Originally Posted by jfugiel
    Does the fact that the fk field in INVENTORY holds an ID that could be the foreign key to one of a number of tables denormalize the database?
    No. The primary key of the INVENTORY table can be the foreign key in as many tables as you would like.
    but blindman, that's not what he's doing, he's not linking those subsidiary tables with FKs that point back the the main INVENTORY table's PK, rather, he's thinking of a field in the INVENTORY table, which will sometimes have a value from this table, sometimes from that one, etc. -- and hence cannot actually be declared as a FK

    which, in my opinion, is bad design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    That can be implemented with GUIDs. I have done it successfully.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2005
    Posts
    5
    r937 is totally correct as to how I am looking to have this work.

    Servers, workstations and whatever else we may add are items in inventory, with common properties/fields such as Name and Location. I would like to store all these properties in the same table called INVENTORY. However, since they posses different detail info (SERVER has a Raid_Configuration field, for instance, and WORKSTATION doesn't), I need a way to relate the two master-detail tables.

    The reason I want to do this is to be able to handle all the things in INVENTORY as a group, say on a summary screen. When adding a new item, say a workstation, the info would get added to the WORKSTATION table as well as a new record being created in INVENTORY. This kind of data setup would then nicely inform the design of objects on the programming side.

    Is there any way to build this kind of relationship into the database schema?

    Thanks for the help and quick responses,

    - Jay

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jfugiel
    Is there any way to build this kind of relationship into the database schema?
    sure, just put the FK into the child table

    each dependent table e.g. WORKSTATION will have an INV_ID field which contains the value of the INVENTORY table's PK field

    whether the INVENTORY table needs to have a discriminator field (which tells you what type of thing it is) is up to you

    to retrieve all fields pertinent to inventory items, use LEFT OUTER JOINs to join the INVENTORY table to all of its dependent tables, and notice that for any given item, only one of the tables will have a match
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    mebbe Im being thick here
    but WHY do you have a separate table for servers and workstations

    are you planning to add a new tabekl for each computer device which isnat a server or workstation (eg router, switch, printer, printer server)

    a device exists if its exists in the warehouse then its in inventory, if it exists in the office in use then its an asset. by all means subclass the product or item to identify what type of computer device it is, but it doens't make data modelling sense to have a separate table for each item type.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Having a "parent" table like inventory, with many potential "child" tables (workstation, server, printer, etc) is an advanced, but fairly common normalization technique called "entity decomposition" in Data Modeling. Is is directly comparable to the technique known as "sub-classing" in Object Oriented Design.

    What you are doing is both possible and more importantly it is correct!

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem
    ...but it doens't make data modelling sense to have a separate table for each item type.
    maybe not to you, eh

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

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by r937
    maybe not to you, eh

    no it doesn't.. but I'm always keen to learn
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jul 2005
    Posts
    5
    Quote Originally Posted by healdem
    but WHY do you have a separate table for servers and workstations
    The reason to have a separate table for each item type is that each of these items has fields which are not in common. You could create an INVENTORY table which combined all fields (instead of just the common ones), but then how would you tell which were appropriate for each device? For instance, INVENTORY would have a Raid_configuration field which would be appopropriate for server records, but not for router records.

    As time went on and you wanted to add new types of items to your inventory (Blackberries, staplers, whatever) you would need to keep adding new fields.

    Also, the logic for which fields get filled in for each type of item would reside wholly in the programming logic.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jfugiel
    Also, the logic for which fields get filled in for each type of item would reside wholly in the programming logic.
    it does anyway, whether you have one master table or several child tables

    in fact, with one master table, it's somewhat easier



    depending on how many "unique" fields there are, i might just go ahead and have only one master table

    say the common table has id and name, and all the other data is dependent on what type of item it is, and there are dozens of these fields -- then i might have common/child tables

    say the master table has id and name and a couple dozen common fields (date acquired, purchase cost, order number, etc.) and there are only a few unique columns per type of item -- then i might have just the one master table with NULLable unique fields

    here's what einstein would've called a gedankenexperiment --

    imagine what the query looks like to return full details of all items from both common and child tables, and picture what the result rows look like, after the query has executed its several LEFT OUTER JOINs, and compare this to the query to return full details of all items from a master table, in which the query will simply return full rows -- aren't there going to be NULLs in exactly the same places in the result rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by r937
    aren't there going to be NULLs in exactly the same places in the result rows?
    Ah yes, but NULL values in a result set and NULL values stored in a column are two very different things in relational algebra. As our fine friend Fabian would point out, NULL values can't be stored in a relational entity.

    Having lit that fuse, I just remembered somewhere across town that I REALLY need to be, very soon!

    -PatP

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    Ah yes, but NULL values in a result set and NULL values stored in a column are two very different things in relational algebra. As our fine friend Fabian would point out, NULL values can't be stored in a relational entity.
    now there's a brilliant tautology that i'm not sure everyone knows, so it bears repeating -- NULL is not equal to anything, even another NULL, so of course NULL values in a result set and NULL values stored in a column are two very different things

    and anyway, so what? when was the last time you pulled stuff out of a database using relational algebra? hint: never -- you used SQL

    and your fine friend has gone totally wacky, zany, bonkers, cuckoo, round the bend -- and while i could take a cheap shot and say that this is due entirely to his preoccupation with how many non-NULL relations can do the polka on the head of a pin, i shall merely point you to what has been occupying his thoughts for the last couple of years, ever since he gave up debunking -- The Fall of Knowledge and Reason

    a word to the wise is sufficient, eh
    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
  •