Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Jul 2007
    Posts
    6

    One Link table w/many Fks or many Link tables?

    I have 33 entities in a new database schema. The first 32 entities can each be related to an unlimited number of records in entity table 33.

    Is it best to create one link table with the 2 foreign keys for EACH of the 32 entities, or to create one link table with 32 foreign keys (pointing to my 32 entities), plus an additional foreign key to entity table 33? I an office full of engineers split down the middle as to which is the best way.

    For example: Entity A is Equipment, Entity B is a Department, Entity C is a Building and so forth. Each of these 3 types of entities can be related to many repair centers. Do we create a link table with 4 foreign keys or 3 linked tables, one for A, one for B, and one for C.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    an office full of engineers, eh?

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

  3. #3
    Join Date
    Jul 2007
    Posts
    6
    They are going to ask me why? Simply saying multiple won't end this debate.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    But Rudy simply saying should
    Ask yourself what kind of relationships these entities have with eachother... In fact, share that with us too
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2007
    Posts
    6
    The only relationship is that all 32 entities require to be filtered based on the union of their join table with the join table of the user's who are attempting to access them. For example: User's with access to repair centers ABC, must only be able to work on entity records whose repair center list contains ABC.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    union of their join table with the join table of the user?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    If you have 33 tables where items in any table might relate to items in any other table then you're going to need a lot of link tables! I tried to work it out mathematically but couldn't think of the correct term (it's not factorial).

    Code:
    If you have 4 tables A,B,C,D
    then the links are between 
    
    A-B, A-C, A-D B-C, B-D C-D
    So with 4 tables you need 3+2+1 link tables. With 33 tables you need 32+31+30+29+...+1 link tables Seems like quite a lot of work to me
    Couldn't you have just one link table that will link to any of the other tables. Obviously you'd have to have the type of entity along with it's id to identify a given item and all the id's would need to be stored in the same type of field.

    You'd want a relationship lookup table that would say which relationships are valid and what they are called.

    Code:
    create table EntityRelationships(
         primaryType      varchar(20),
         primaryId       varchar(20),
         secondaryType     varchar(20),
         secondaryId      varchar(20),
         relationshipName     varchar(20)
    )
    
    create table AllowedRelationships(
         primaryType      varchar(20),
         secondaryType     varchar(20),
         relationshipName     varchar(20),
         relationshipDescription           varchar(100)
    )
    
    create table AllowedEntityTypes(
         entityType      varchar(20),
         description      varchar(100)
    )
    So your choice boils down to
    1. 3 tables and a few compromises
    2. or perfect formal design but potentially 100's of tables and tonnes of code supporting it.
    Each to their own I guess.

    Mike

    PS Gary - it would make it much easier to understand the issue (for me anyway) if you describe everything in plain english.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Mike, no.
    P.s. Why is everything varchar(20) ?!
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    come on, mike, don't hold back, go ahead and send him your "off the shelf" EAV solution

    and with that, i'm out of this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2007
    Posts
    6
    So, let me try to recap the scenario again:

    Basically, I have the following DB model to filter records from 32 tables which contain information from different facilities. A facility can have one or more repair centers. Think of a chain of hospitals (one organization with many facilities)

    Tables:

    - 1 user table, (User can work for one or more repair center)

    - 1 RepairCenter table

    - 32 other entity tables (departments, buildings, equipment, tasks, projects, hazardous material, warehouses,etc.)



    Relations:

    - 1 user (= 1 record) can be linked to multiple RepairCenters (= multiple records)

    - 1 Repaircenter (= 1 record) can be linked to multiple users (= multiple records)

    - An entity table can be linked to multiple RepairCenters (= multiple records)

    - 1 RepairCenter record can be linked to multiple entity tables



    Business Logic:

    A user is linked to X number of repair centers. Whether or not the user may work with data in a certain entity table depends on if there is a link between that entity table and one of the RepairCenters the user is linked to.



    So far correct?



    This would give me the following datamodel:



    Table users

    columns: user_id



    Table user_repaircenter

    columns: user_id, repaircenter_id



    Table repaircenters

    columns: repaircenter_id



    Table repaircenter_entity

    (This is where we tying to decide if we need 32 or single 'join' table)

    columns: repaircenter_id, entitytype_id (32 foreign keys if a single table?)



    Table entities

    columns: entitytype_id, entity_name,

    I hope this clearer....

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Here's a really simple solution (using department as our basis for security).
    Security(UserID, Department)
    Code:
    SELECT *
    FROM employees
    WHERE department IN (SELECT department FROM security WHERE userid = <insert user id>)
    Very basic, very simple.

    Another method would be to use security groups. Similar to above but you have GroupID instead of userid and then you add a column to your users table called GroupID which is your related field.

    EDIT: Just read your previous post - second suggestion wold be best methinks
    George
    Home | Blog

  12. #12
    Join Date
    Jul 2007
    Posts
    6
    We are not interested in any Entity-Attribute-Value model. We are getting rid of anything that resembles EAV in our current schema!

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Good for you Gary!!
    Anyhoo - I'll take a better look at this when I have some more time...
    George
    Home | Blog

  14. #14
    Join Date
    Jul 2007
    Posts
    6
    Mike said 'If you have 33 tables where items in any table might relate to items in any other table then you're going to need a lot of link tables!'.

    This isn't true, these items are NOT related except they need to be filtered by the user's repair center list. I think r937 is correct to say to use 32 join tables.

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Originally posted by georgev
    P.s. Why is everything varchar(20) ?!
    Because I haven't been told what type/size of ids he uses. I thought it best to put something down as a guess and left it at varchar(20). I was going to put in a comment to say these types were just place holders but forgot. Perhaps with your vast experience you could think of some better way of putting the idea across?

    Originally posted by r937
    come on, mike, don't hold back, go ahead and send him your "off the shelf" EAV solution

    and with that, i'm out of this thread
    Nope - I'll keep to the question at hand. Anyway bye again

    Originally posted by Gary R. Schaecher
    We are not interested in any Entity-Attribute-Value model. We are getting rid of anything that resembles EAV in our current schema!
    Who proposed EAV here? I simply responded to (what I thought was) your original question your "how do I provide links between all 33 entity types".


    Originally posted by Gary R. Schaecher
    Mike said 'If you have 33 tables where items in any table might relate to items in any other table then you're going to need a lot of link tables!'.

    This isn't true, these items are NOT related except they need to be filtered by the user's repair center list. I think r937 is correct to say to use 32 join tables.
    Fair enough but your original description was very hard to decipher. I guess if you spent a little more time describing your problem then I'd of been able to provide a more relevant solution - or most likely left it alone alltogether.

    Mike

Posting Permissions

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