Soldato
- Joined
- 27 Dec 2005
- Posts
- 17,316
- Location
- Bristol
I'm setting up a website that has two user types (on a basic level): effectively individuals (buyers) and businesses (sellers). Sellers will never/rarely be buyers and visa versa so I'm keeping these two separate. The current database setup is this:
My main question is should I link 'users -> individuals' and 'users -> suppliers' via the userid key in the 'individuals' and 'suppliers' tables as currently, or put the userid key in 'users'. Either way, if they usertype key is ever lost/corrupted then there'd be two duplicate ids (one individual, one supplier) for every id. The only way I can see of preventing that is merging the individuals and suppliers tables, but this would mean there'd be a lot of excess/unused data for individual accounts.
Code:
TABLE: users // deals with all the login details and those that are common across both individuals and businesses. usertype denotes if they're an individual (1) or business (2).
id INT(9), PRIMARY
email VARCHAR(255), UNIQUE
title VARCHAR(4)
gender INT(1)
name VARCHAR(255)
surname VARCHAR(255)
username VARCHAR(50)
password VARCHAR(32)
emailconfirmed INT(1)
usertype INT(1)
Code:
TABLE: individuals // contains the other data for individuals
id INT(9), PRIMARY
userid INT(9), UNIQUE // links to 'users'
dob VARCHAR(10)
newsletter INT(1)
region INT(3)
Code:
TABLE: suppliers // contains the other data for businesses (and their profiles)
id INT(9), PRIMARY
userid INT(9), UNQIQUE // links to 'users'
businessname VARCHAR(255)
address1 VARCHAR(255)
address2 VARCHAR(255)
address3 VARCHAR(255)
region INT(3)
postcode VARCHAR(8)
businesstype VARCHAR(50)
servicetype INT(3)
regionscovered VARCHAR(150)
website VARCHAR(255)
telephone VARCHAR(30)
publicemail VARCHAR(255)
accounttype INT(2)
balance VARCHAR(10)
image1 INT(9)
image2 INT(9)
image3 INT(9)
updates INT(1)
description LONGTEXT
newsletter INT(1)My main question is should I link 'users -> individuals' and 'users -> suppliers' via the userid key in the 'individuals' and 'suppliers' tables as currently, or put the userid key in 'users'. Either way, if they usertype key is ever lost/corrupted then there'd be two duplicate ids (one individual, one supplier) for every id. The only way I can see of preventing that is merging the individuals and suppliers tables, but this would mean there'd be a lot of excess/unused data for individual accounts.