Relationship question

[Recoil UK]

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

[jofa]

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...")

[Recoil UK]

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

[jofa]

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

[Recoil UK]

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]

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

[Recoil UK]

Hello

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

L8rs