Database Question: User Relationships

Russinating · 2 Sep 2010 at 10:15

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:

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.

Goksly · 2 Sep 2010 at 10:40

Personally I'd have the 'users' table as the parent table and then have the userid as the primary and foreign key in the other tables (there is only going to ever be one row for that user in those tables, so use that as the key).

If they key is ever corrupted (not that it should ever happen?) you'll be able to work out the type from the data in the other tables and then (as you said) if there are both you can manually work out which type the user should be.

Don't think you should worry about loss / corruption - just keep regular backups

Russinating · 2 Sep 2010 at 11:13

Thanks.

On a database note, what's the smallest field type to use for a simple TRUE/FALSE (ie 1/0) storage? Just to be used for things like newsletter, gender, emailconfirmed etc.

Russinating · 2 Sep 2010 at 11:16

Double.

SimonCHere · 2 Sep 2010 at 12:26

CHAR(1) is the smallest for boolean values

Pho · 2 Sep 2010 at 13:00

MySQL 5+ has a BIT type which only has allowed values of 0/1.

Russinating · 2 Sep 2010 at 13:37

Pho said:
MySQL 5+ has a BIT type which only has allowed values of 0/1.

I assume that's when I put the length as 1?

GravyMonster · 2 Sep 2010 at 14:04

Hi Russ - just saw your email!

I think Goksly has answered this perfectly for you

SimonCHere · 2 Sep 2010 at 16:51

Pho said:
MySQL 5+ has a BIT type which only has allowed values of 0/1.

That's true. It depends on his DB I suppose.

Xelene · 2 Sep 2010 at 19:07

Should be a BOOL available on MySQL, otherwise use Tiny Int or Byte.

User ID should be in users table, use foreign keys to link the other tables so if a user is changed it automagically updates in the other tables.