Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2010
    Posts
    10

    Unanswered: OTLT - that old cheasnut.

    Right.

    Here goes... I appreciate this can cause a mass discussion - which is a good thing in my book

    I am in the early database design stages of a web application I will be developing over the next 24 months.

    I am using GUIDs as there will be a certain amount of global replication happening.

    So, the IDs are as unique* as can be in the database.

    I have an AddressBook table which as it sounds holds information relating to several other (currently 4) tables via a column called PK_ReferenceID (uniqueidentifer data type).

    Now, for this table, I feel its a good approach. However this is a big application and I want to check and check again (hence registering on this forum and starting this topic) that this is an agreeable approach (not the best, but its acceptable) on having one AddressBook as a lookup table across the 4 tables (which may increase over time - infact almost certainly will).

    I have added FK constraints in the draft design I am putting together. I am now in a position where another lookup table could work in the same manner.

    However, I am unsure as to whether to use this approach.

    The other table is an Employee SkillSet table, Work Visa Table, which could be joined up to the primary Employee table via one lookup table...

    Question is... should I?

    We have big new servers, big broadband etc...

    I am still wary of performance costs though.

    Anyone make any recommendations? Should I have separate look up tables across the Employee table relationships?

    Should I have separate lookups for the AddressBook?


    *as far as I am aware, if there is better way than newid() under unqiueidentifier data type I'm all ears

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by skuff View Post
    I have an AddressBook table which as it sounds holds information relating to several other (currently 4) tables via a column called PK_ReferenceID (uniqueidentifer data type).
    this part is unclear

    could you describe the table relationships further?

    Quote Originally Posted by skuff View Post
    The other table is an Employee SkillSet table, Work Visa Table, which could be joined up to the primary Employee table via one lookup table...
    this, too, needs further explanation

    we don't know the contents of these tables or what they're for or how they should be related
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    10
    AddressBook is a central table for holding address information.

    There are tables that relate to this:

    Company - PK CompanyID : uniqueidentifier (newid())
    Supplier - PK SupplierID : uniqueidentifier (newid())

    AddressBook has address information etc...

    In this table is a column called PK_ReferenceID (uniqueidentifier)

    My thoughts are to join Company and Supplier tables into AddressBook with the relevant table Primary Keys (CompanyID and SupplierID) onto PK_ReferenceID

    This will however be a large application and I have concerns about performance against the AddressBook table once it is populated with large amounts of data.

    The Employee Tables are:

    Employee - PK EmployeeID (uniqueidentifier (newid())
    EmployeeSkill - PK EmployeeSkillID (uniqueidentifier (newid())
    EmployeeVisa - PK EmployeeVisaID (uniqueidentifier (newid())

    There will be vairous other Employee tables

    I could have one lookup table between the primary table Employee for EmployeeSkill and EmployeeVisa using the same design as the AddressBook mentioned above.

    I would envisage the lookup table from Employee having:

    EmployeeID and PK_ReferenceID

    PK_ReferenceID could be either a link to EmployeeSkillID OR EmployeeVisaID depending upon what query I run against the database.

    My question is should I do this OR have many lookup tables

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by skuff View Post
    My thoughts are to join Company and Supplier tables into AddressBook with the relevant table Primary Keys (CompanyID and SupplierID) onto PK_ReferenceID
    this is the part i don't understand

    maybe it's just me

    Quote Originally Posted by skuff View Post
    I could have one lookup table between the primary table Employee for EmployeeSkill and EmployeeVisa using the same design as the AddressBook mentioned above.
    again, i don't get it

    perhaps you could mock up a few sample rows toshow the structure of the relationships

    Quote Originally Posted by skuff View Post
    PK_ReferenceID could be either a link to EmployeeSkillID OR EmployeeVisaID depending upon what query I run against the database.
    this sounds dicey

    i don't think you can succesfully declare a foreign key to operate against more than one table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    10
    There is no data in the database yet - its purely a draft design - I firmly believe you never get it right first time on large databases so be prepared to draft, draft and draft again - the sign off process will always require amends and I like reference points during the design stage.

    Anyway, erm, whats the best best way for me to show you...

    All I have done is added relationships,

    ADDRESSBOOK TABLE
    AddressBookID
    FirstName
    Surname
    JobTitle
    AddressLine1
    AddressLine2
    AddressLine3
    [Country/Region]
    [PostCode/ZipCode]
    CountryID
    TelephonePrimary
    TelephoneSecondary
    Fax
    IsPrimaryAddress
    PK_ReferenceID : Foreign key Reference
    DateCreated
    DateModified
    ModifierUserID
    Active


    SUPPLIER TABLE
    SupplierID : PK
    Company
    Discount
    SupplierStatusID
    DateCreated
    DateModified
    ModifierUserID
    Active

    COMPANY TABLE
    OfficeID : PK
    Name
    DateCreated
    DateModified
    ModifierUserID
    Active


    So my query for getting Supplier addresses would be

    SELECT
    AddressBook.*

    FROM
    AddressBook

    INNER JOIN
    Supplier ON AddressBook.PK_ReferenceID = Supplier.SupplierID


    Similar approach for Company address, substituting relevant PK etc...

    NOTE: Maybe you have misunderstood me as I have just read what I have put again.

    I don't mean I will be joining all the tables at the same time. The AddressBook table is purely a table I can get addresses from for either a Supplier OR a Company - using the PK_ReferenceID (which should really be called FK_ReferenceID - why did I put PK amend draft) for either query
    Last edited by skuff; 03-19-10 at 12:31.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why are Company and Supplier separate tables? They are both companies, or "Entities", with addresses. Combine them, and link it once to the address table.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, i see what you're doing, and in my opinion it is, um, suboptimal

    your "FK" referring to multiple tables isn't really a foreign key at all, you're just trying to treat it as though it were

    a true foreign key is implemented in DDL (data definition language)

    you can still keep the address table, if you put a foreign key into each of the company/supplier/etc. tables

    from the looks of it, each address is unique to the entity that owns it (i.e. addresses aren't shared) because you've got the phone numbers in the address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Address is a property of a supplier (or company), so in my view the FK should be the other way around.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    And it is not an OTLT, by the way.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Mar 2010
    Posts
    10
    Quote Originally Posted by blindman View Post
    Why are Company and Supplier separate tables? They are both companies, or "Entities", with addresses. Combine them, and link it once to the address table.
    Is this circumstance no.

    The Company is our internal companys. Its an Asset management application for our global business.

    We have high valued equipment which can either be Internally owned (Asset) by one of our companies or externally provided from one of our Suppliers.

    The equipment is then hired out at various locations at various costs with shipping, project work flow and all sorts etc..

    The AddressBook table stores all addresses for any record needing that relationship.

    Hence the PK_ReferenceID in AddressBook table.

    I can grab addresses based on a join in a query from any primary table requiring by joining PK_ReferenceID onto the PK of the primary table (i.e. Company or Supplier)
    Last edited by skuff; 03-19-10 at 12:51.

  11. #11
    Join Date
    Mar 2010
    Posts
    10
    Quote Originally Posted by n_i View Post
    And it is not an OTLT, by the way.
    Ok I know, but it's not far off.

  12. #12
    Join Date
    Mar 2010
    Posts
    10
    Quote Originally Posted by blindman View Post
    Why are Company and Supplier separate tables? They are both companies, or "Entities", with addresses. Combine them, and link it once to the address table.
    I have also re-named the Company table for this forum as its currently an internal name which I don't want to put on here - sorry. Powers that be and all that

  13. #13
    Join Date
    Mar 2010
    Posts
    10
    Quote Originally Posted by r937 View Post
    okay, i see what you're doing, and in my opinion it is, um, suboptimal

    your "FK" referring to multiple tables isn't really a foreign key at all, you're just trying to treat it as though it were

    a true foreign key is implemented in DDL (data definition language)

    you can still keep the address table, if you put a foreign key into each of the company/supplier/etc. tables

    from the looks of it, each address is unique to the entity that owns it (i.e. addresses aren't shared) because you've got the phone numbers in the address
    Thas indeed correct.

    It's something I want to check out. This is also what I am thinking about using for the Employee information.

    One table which stores the ghost FK as it were against the EmployeeID which I can then run a query against to get either EmployeeSkills info OR EmployeeVisa info.

    Just after thoughts, should I, shouldn't I... should I go down the route of having one look up table from Employee to link to EmployeeSkills and another for EmployeeVisa?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think what you should do is a bit more research into how foreign keys actually work

    your idea for "ghost" keys is in the wrong direction
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by skuff View Post
    Is this circumstance no.

    The Company is our internal companys. Its an Asset management application for our global business.

    We have high valued equipment which can either be Internally owned (Asset) by one of our companies or externally provided from one of our Suppliers.

    The equipment is then hired out at various locations at various costs with shipping, project work flow and all sorts etc..

    The AddressBook table stores all addresses for any record needing that relationship.

    Hence the PK_ReferenceID in AddressBook table.

    I can grab addresses based on a join in a query from any primary table requiring by joining PK_ReferenceID onto the PK of the primary table (i.e. Company or Supplier)
    This actually just confirms my suspicion that these two entities should be combined into a single table. They both have names. They both have addresses. They are both companies. They both own equipment which is hired out to locations.
    Combine them into a single table with a flag indicating whether they are internal or external, and I predict many of your design problems will evaporate. You are focusing on a logical model in which the entities are separate, but in moving to a physical model the entities may be combined, or split, or whatever. Logical ERD <> Physical ERD.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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