Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78

    Question Unanswered: Table Relationships

    My database is coming on nicely, one thing is bothering me though, eventually the db will hold account information so I want to build this in now to save later embarrassment!
    The tables are:
    COMPANIES

    DEPARTMENTS

    ACCOUNTS

    CONTACTS

    My problem:
    Not all Contacts are from Companies
    Some Contacts have Accounts some do not
    Some Companies have Accounts some do not
    Some Companies have Departments some do not

    I just can't seem so see the best way to arrange these relationships. At the moment I have the contact table as the main table:
    CONTACTS
    companyID
    DeptID
    AccountNo

    And if someone is not from a company and not from a dept I have a record in the company table ‘NOT A COMPANY’ and the department table ‘NO DEPARTMENT’. This does not seem like an elegant solution.
    Any ideas?
    the light is on, someone is home, but they dont know they are. HELLO!

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    I thing this:

    CONTACTS ---- Link ----- COMPANIES --- Link --- DEPARTMENTS
    ---- Link --- ACCOUNTS

    My be this can work. But don't forget that your system have rules for both else you never ending and don't have time for the big problem.
    Saludos
    Norberto

  3. #3
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Originally posted by Norberto
    I thing this:

    CONTACTS ---- Link ----- COMPANIES --- Link --- DEPARTMENTS
    ---- Link --- ACCOUNTS

    My be this can work. But don't forget that your system have rules for both else you never ending and don't have time for the big problem.
    Thanks Norberto
    the light is on, someone is home, but they dont know they are. HELLO!

  4. #4
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i think we need more data

    you say
    Not all Contacts are from Companies
    Some Contacts have Accounts some do not
    Some Companies have Accounts some do not
    Some Companies have Departments some do not

    how about
    do all companies have contacts
    do all accounts have contacts
    do all accounts belong to companies
    are all departments in a company


    a simple rule of thumb is that if something has 1 and only 1 of something then that is where it's recorded
    ie
    a company might own many cars
    but a car can only be owned by a single company

    in this case the car holds the company id
    this is also known as a 1 to many relationship
    the other main types you might have are 1 to 1 and many to many
    1 to 1 should normaly be merged into 1 table if this wont work then you give one of them (BUT NOT BOTH) the id field of the other,
    M to M need to be split in to a 1 to M to 1 relationship with the M being a linking table

    ie a customer might order many products and a product might be ordered by many customers

    in this case you add a order table where the order links each customer with 1 part where each part in other words a order only has 1 part and 1 customer but a customer or part can have many orders and the order holds the id's of both fields

    Lecture Mode OFF

    hope this is of some use
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  5. #5
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    I am running out of time to get this db in place. Is it a problem to go back to a database once it is up and running and change elements of its design, as I'm sure I haven’t thought of everything yet and once the users get hold of it they will find ‘features’ that they are not so keen on

    At some point iv'e just got to go for it.
    the light is on, someone is home, but they dont know they are. HELLO!

  6. #6
    Join Date
    Nov 2003
    Posts
    267
    There isn't a problem in going back and changing things later, it just takes more time then setting it up right the first time. However it is rare to get everything perfect the first time around (users always seem to forget things then need). Just recognize that when you change data structure, after the fact, you need to know what kind of impact that will have on everything else and be good a testing.

    s-

  7. #7
    Join Date
    Aug 2003
    Location
    UK
    Posts
    78
    Thank you
    the light is on, someone is home, but they dont know they are. HELLO!

Posting Permissions

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