SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Relationship question

    Hi guys

    You all seem very knowledgable on this forum, so I thought you may be able to help me out.

    My problem is this....

    I have designed the following tables

    users
    userid - pk
    contactid - fk
    typeid - fk
    title
    firstname
    surname
    username
    password

    addresses
    addressid - pk
    addressdata
    linenumber

    contacts
    contactid - pk
    telephonenumber
    faxnumber
    emailaddress
    county
    postcode

    usertypes
    typeid - pk
    typedescription

    businesses
    businessid - pk
    addressid - fk
    contaactid - fk
    businessname

    my problem is that i,m not sure how to complete the last relationship,which is between the users, adresses and usertype. You see, not every user will need an address, this in itself is dependant on the usertype.

    There are 3 options at present..

    public user (this is a person that will only ever enter data once,but needs an address)

    business user (this is a person that will enter data in future tables not supplied, the address will be taken from the root business users address)

    root business user (this is the person that will setup the account and have the rights to create new users that can post details on a website)

    Now should I just add an addressid field to the users table and axcept null entries or is there a better way?

    BTW this will be setup with the mySQL database.

    Hope all that makes sense, and I hope someone out there can help.

    Thx in advance
    Recoil UK

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Create a user_address table;
    user_id fk
    address_id fk
    is_default (1/0) <= allow a user to have multiple addresses stored, and one of them selected as the default address

    What is the relationship business user <-> root business user?
    Don't you need a rootuser_id field in users?
    e.g. user_id = 123 => rootuser_id = 99, rootuser_id = 99 => address_id = 101, address_id = 101 => addressdata = "2510 Fairview Avenue East, Seattle, WA 98102, USA"

    Addressdata??
    Why not street, number, city, zip, state, country fields?

    Why postcode and country in contacts and not in addresses?

    users.title not a fk? (Use a lookup table containing "Mr, Professor, Sir...")

  3. #3
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again

    Your absolutely correct in the things you said previously.

    I think I was taking the term "repetitive data" to literally.

    I,ve now revised my tables, tell me what you think.

    user_details
    user_id - pk
    title_id - fk
    firstname
    surname
    street
    city
    county_id - fk
    postcode
    telephone
    email
    username
    password

    titles
    title_id - pk
    title

    counties
    county_id - pk
    county_name

    company_details
    company_id - pk
    name
    street
    city
    county_id - fk
    postcode
    telephone
    email

    agent_details
    agent_id - pk
    company_id - fk
    is_master_agent (1/0)
    jobtitle
    department
    telephone
    email
    username
    password

    What do you think?
    I could probably put the city and postcode fields in there own tables, like the counties, but there are so many cities and postcodes are allways changing, it would be more of a headache to keep them upto date.

    I also belive that because i,m using mySQL, the foreign key relationships would have to be enforced in my php and sql code, is this correct?

    Thx for your help
    Recoil UK

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What happened to addresses? I think it's a good idea to have a separate table for them, you never know if you want to let the users/companies have multiple addresses in the future (e.g. billing address / delivery address)

    department - text or id?
    Or... it's not "your" departments?
    I was thinking of a structure like
    person>-department>-company

    Yes, you have to use the php code to enforce reference integrity
    The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything.
    MySql manual

  5. #5
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again

    I think I will add the address table again, and make the primary key in company_details a combination of company_name-address_id. Why you ask? well.....(oh, if I did this would I need to change the fk's that depend on it or is it possible to have fk's not dependent on pk's)

    I,m assuming that each company will have a headoffice where all bills will be sent/paid, but some companies dont work like that, and each department/offices may require to pay there own bills, this would allow it. I would then not need to have the department filed in agent_details, as the purpose of the database is not to track which department they are in, the master_agent can do that him/herself.

    That is ofcourse if mySQL will allow you to make a combined primary key while still allowing one field to auto-increment?

    Then it will also be easier to implement a billing_details table if I decide to, this would consist of billing_id, company_id and address_id.

    What do you think?

    Thx for the help so far, its appreciated.
    Recoil UK

  6. #6
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again

    I,ve been thinking about this and have finally come to a solution.

    I,ll post a gif of the schema if anyones interested.

    Thx guys
    Attached Images Attached Images
    Recoil UK

  7. #7
    SitePoint Member
    Join Date
    Oct 2002
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello

    Version 2, with the business details included lol, my mistake.

    L8rs
    Attached Images Attached Images
    Recoil UK


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
  •