mysql db design question

[beeker]

Hello,
I am creating a site for a friend of mine, needless to say i am way over my head but I dont like to walk away from a chalenge

the db will be used for his shipping based company. The idea is that they go to the site, enter all the data, where its going where its comming from details of the shipment, whos paying for it etc...

currently i have each section broken down into different tables all of which have a orderid foreign key that ties to a order table... that table simply has the orderid and the ids for all the other tables that corespond to that table...

I am not sure if this is the proper way to design this or is it better to have 1 huge table for each order... and a couple small tables for customer records etc.

Too me the way it is being setup currently makes sence to me but I am not sure if having the data from the php form and appropriate table id will update in the correct areas by clicking the single submit button.

Let me know if it will help by wrighting out the tables here...

Thanks in advance
Keith

 

[jnjc]

Sounds like you may have gone a bit too far with Normalizing your data. Having the Order table with just a load if Id's in it will make it very cumbersome to work with. You are on the right track. You should probably structure your files along the lines of:

Order Header Table:

Order Number
Customer Number
Order date, Dues date etc.
Delivery address, if different than customer address (there are arguments for and against having this in a separate table)
Order Total (Again there are lots of arguments for and against this, I tend to have it for ease of reporting)

Order Detail Table
Order Number
Line Number
Item Code
Qty
GST Code
Unit Cost Inc. GST
Unit Cost Ex. GST
Discount
etc...............

By structuring the table this way it's still fairly readable whilst conforming to 3NF rules.

HTH,
JC

 

[beeker]

That sounds good, and what i was afraid of all at the same time

 

[beeker]

I am trying to re-arange the data according to the suggestion in the forum... I do have 1 question (actually allot more but we will start with this one today )

i have certain data fields that will be the result of a dorp down selection. For example charge_to will have the options of shipper, consignee, and 3rd party.

Would this be best handled in the php form (I just type in the options. Or a drop down that pulls the options from a table in the db?

thanks

 

[jnjc]

If budget and Time allows you are better off putting these in a DB table. The main reason is it allows for easy expansion.

There are a couple of ways to handle this.

A) Have a dedicated table for each drop down. The table layout will be something like:

1) ChargeToId
2) ChargeToDesc
3) Actual Charge (or something along that line ?)
4) Any other additional field...

This is good for things like delivery type etc where there is a different charge value for each delivery type.


B) If there is not additional information associated with the drop down then you could use a control record to handle multiply drop downs. Format would be:

1) Record Type
2) Record ID
3) Record Desc


So for the "Charge To" drop down look for record type "ChargeTo" and so on for the other drop downs.

It makes coding easier because you can put a function togther called putDropDown. Call it like putDropDown("label","RecordType") and use it for every drop down contained in the table...

So you will probably need a mixture of both depending on the usage.

These methods will keep your 3NF (Third Normal Form) rules intact. On you order record you have a ChargeTo Field with the RecordID in it.

HTH,
JC

 

[beeker]

sounds good... Now just to make sure i understand im going to write out a few examples...

A) Drop down that does or could have aditional information
create table DD_ServiceLevel (DD_servicelevelID, description, price, etc)
1 - next day - $xx.xx
2 - 2 day - $xx.xx


B) Drop down for straight forward lists
create table DD_weight (DD_weightID, description)
1 - LB
2 - KG

create table DD_chargeto (DD_chargetoID, description)
1 - Shipper
2 - Consignee
3 - 3rd Party

Thanks again, and is it ok to use the label description multiple times like i did above, couldnt think of a better label name.

 

[jnjc]

I am not sure you have the right end of the stick with drop downs that are just a list. To do these I would have one table that holds the data for multiply drop downs. Something like

Table Name: DD_DropDowns

1 DropDownType
2 Key
3 Description

Then when you do a drop down for say "Weight" you would use something like

select "*" from DD_DropDowns where DropDownType = "Weight" ....

If the drop down was for Title then

select "*" from DD_DropDowns where DropDownType = "Title"

As for using the label Description multiple times, as far as I'm concerned that's good practice, at least you know what it'll be called irregardless of the table...

HTH,
JC

 

[beeker]

I believe i am turning the stick around lol...

having 1 table is much cleaner, I just didnt know you could do that, nor did I think of it as a possibility either.

Thanks again...