table design question

Manat

Hello all,

I'm creating an online warranty registration section on our site.
I have 3 tables, warranty table, customerInfo table and userRegistration table.

Primary keys in all tables is custID(as in customer ID).
Now should custID be an auto_increment(autonumber) in every table? Or should it just be an auto_increment field in one table, take that value and put it in other tables, if so then which table would be the deciding custID table?

What would be the optimal route to take?

And also should custID be a primary key in every table?

Thanks
MK

bastien

Pk in one (customer table) table
no, each table should have its own Pk

customer
custID
name
addr
etc

warranties
warrID
custID (FK relationship if myISAM supported FK)
etc

userRegistration (how is this diff from customer table?)
userID
etc

Manat

Hi,

Ok, so custID should only be PK in the customer table.

The difference between userRegistration and the Customer table is the customer table has the person's name, address, email and all contact info.. userRegistration has custID, username, password, signup date, last login ect..

Here is my SQL code for each table:

UserRegistration Table:
CREATE TABLE userRegistration (
custID INT( 5 ) NOT NULL ,
username VARCHAR( 10 ) NOT NULL ,
password VARCHAR( 10 ) NOT NULL ,
info VARCHAR( 30 ) NOT NULL ,
userLevel INT( 2 ) NOT NULL ,
signupDate VARCHAR( 10 ) NOT NULL ,
lastLogin VARCHAR( 10 ) NOT NULL ,
activate INT( 2 ) NOT NULL ,
misc VARCHAR( 10 ) NOT NULL
);

Customer Table:
CREATE TABLE customerInfo (
custID int(5) NOT NULL default '0',
fname varchar(13) NOT NULL default '',
lname varchar(13) NOT NULL default '',
gender char(2) NOT NULL default '',
address varchar(20) NOT NULL default '',
city varchar(14) NOT NULL default '',
stateProv varchar(20) NOT NULL default '',
country varchar(14) NOT NULL default '',
zipPostal varchar(10) NOT NULL default '',
tel varchar(14) NOT NULL default '',
email varchar(20) NOT NULL default '',
contact int(1) NOT NULL default '0',
misc varchar(4) NOT NULL default ''
) TYPE=MyISAM;

Warranty Table:
CREATE TABLE warranty (
custID int(5) NOT NULL auto_increment,
wtyID int(5) NOT NULL default '0',
skuNum int(7) NOT NULL default '0',
batchNum varchar(14) NOT NULL default '',
purchaseDate varchar(20) NOT NULL default '',
date varchar(20) NOT NULL default '',
PRIMARY KEY (custID),
KEY skuNum (skuNum)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

What in your opinion should be the PK in every table?
Also, what i was planning to do is link custID to every custID in both other tables, with Customer being the linking table, would this be a correct approach?

Also, are you saying that custID should ONLY be an auto-increment in the customer table and not the other ones?

Thanks
MK

bastien

custID should be the only autincrement PK for customer info...should be treated as just another piece of data for an insert...

wID (new col autoincrement PK) for the warrenty table. as users may have more than one claim per warranty number

CustID could work for userRegistration table as long as your application can ensure that the customer can only register once...which means checking every time a customer wants to sign up, have a lost password function, change password function etc...

As to your linking question...that is the correct approach..

Manat

Hi,

Yes but wtyID can't be a PK, because custID in the warranty table would the be the PK. there would only be one unique custID, but one custID can have many wtyIDs.

i.e. custID 3 has 4 wtyIDs
custID 7 has 2 wtyIDs. so wtyID =1 and = 2 appear twice.

Just to make sure, so custID in warranty shouldn't be auto-increment and custID in userRegistration should also be NOT an auto-increment.

Or should custID only be an auto-increment in customers table and it takes that int and brings it to other tables.
Sorry for asking this many times, it's just unclear.

Thanks alot

bastien

I assumed that wID was/is a fixed number (as in here is your warranty certiifacte with number blahblahblah). If is merely a record of how got serviced, then its fine as a PK...

NOTE: PKs CAN NOT BE DUPLICATED IN A TABLE...by definition they are unique...

CUSTID in warranty and userRegistration SHOULD NOT BE autoincrement as you can not guarantee that the users will come in order and that would mix them up reducing your links to crap...

Or should custID only be an auto-increment in customers table and it takes that int and brings it to other tables.

CORRECT...

Manat

Hi,

in CustomerInfo, PK is custID
in Warranty, PK is custID
in userRegistration, PK is username

because the primary key is userRegistration is username, i cannot link customerInfo.custID to userRegistration.custID as a relationship.

Also, even if userRegistration.custID links to customerInfo.custID, then customerInfo.custID cannot link to warranty.custID.

Can't I have customerInfo.custID link to two fields in two different tables? It's a drop down box, i only seem to be able to link to one.

What should I do? I don't think that warranty.custID should be the one linking to userRegistration.custID.

Thanks
MK

bastien

i cannot link customerInfo.custID to userRegistration.custID as a relationship

You just did That is exactly how to link the tables in sql

$sql = "Select ci.custID, ci.custName, ur.userName from 
customerInfo ci, userRegistration ur where
ci.custID = ur.custID and ci.custID = 1";

There is no requirement to link on primary keys...in many cases primary keys are irrelevant to the actual data (they have no meaning other than a unique key for the table)

No, there is no two table limit, I have joined up to 10 tables together.

I don't think that warranty.custID should be the one linking to userRegistration.custID.

Yes, you can do this, but it only needs to be done if you need information from both tables at the same time.