DB Design help

[vchris]

I'm starting a new project. Creating my first ecommerce site for a client. The billing will be handle by another website. I have to hold some of the customer info, products, categories and orders. Here is my DB design.

IPD database Products table:prod_idcat_idprod_nameprod_descriptionprod_priceprod_colourprod_imgprod_delprod_special Categories table:cat_idcat_name Customers table:cust_idcust_fnamecust_lnamecust_emailcust_phonecust_addresscust_citycust_zipcodecust_countrycust_paymethod Orders table:ord_idcust_idprod_idord_quantity

So let's say customer 1 orders product 1, orders table will look like this:1, 1, 1, 1Now what if he orders 2 or 3 items with different quantity for each? Should it be a different order for each product or is there a better way to handle this?

[Reg Edit]

I would use:

Orders table:ord_idcust_idOrderproducts table:ordprod_idord_idprod_idord_quantity

That way, anything pertaining to the whole order (and you may well add others such as order_date) is stored once on the orders table, and anything pertaining to an individual product (such as quantity) is stored for each product in the order.

[vchris]

So if the customer has chosen product 1 and 2 and the second one has a quantity of 3 the database will look like this:Orders table:ord_id, 1cust_id, 1Orderproducts table:ordprod_id, 1ord_id, 1prod_id, 1ord_quantity, 1ordprod_id, 2ord_id, 1prod_id, 2ord_quantity, 3Right?

[Skemcin]

How about this:

IPD databaseProduct (oe_product):prd_IDprd_nameprd_descriptionProduct Attribute (oe_product_attribute):pra_IDpra_prd_IDpra_pricepra_colourpra_imgpra_sizepra_weightpra_specialProduct Media (oe_product_media):prm_IDprm_prd_IDprm_filenameprm_widthprm_heightprm_formatprm_extensionCategory (oe_product_category):cat_IDcat_codecat_namecat_descriptionCustomer (cu_customer):cst_IDcst_fnamecst_mnamecst_lnamecst_unamecst_pwordcst_email1cst_email2cst_phone1cst_phone2Customer to Address (cxa_customer_x_address)cxa_IDcxa_cst_IDcxa_adr_IDAddress (cu_address):adr_IDadr_line1adr_line2adr_cityadr_zipcodeadr_countryadr_typeOrder Summary (oe_order_summary):ord_IDord_cst_IDord_subtotalord_shippingord_taxord_totalord_paymentmethodord_statusOrder Item (oe_order_item):ori_IDori_ord_IDori_prd_IDori_pra_IDori_quantityGlobal Fields (put on all tables):xxx_add_userxxx_add_datexxx_change_userxxx_change_datexxx_delete_flag

There are a couple things that you will run into with your design that might trouble you sooner than later.

• Seperate your address information from your customers - you have to consider shipping versus billing. Even if you don't care now, just do duplicate inserts, updates, and deletes.
• Seperate your products from your attributes - if I'm selling widgets, I doubt they will be all the same color, shape and size. Keeping a parent product allows for better select statements and make adding more types/prices easier
• Rarely do folks only want one image - now-a-days, you have self proclaimed videographers - make a one-to-many relationship and put your multimedia records in a separate table.
• Add username (uname) and password (pword) fields to your customer table so users can create an account and come back to check on orders and look at history.
• If you don't normalize email and phone, then add a couple in the customer table for some flexibility
• Just like customer to address and product to attribute, your order to order item data needs to be seperate. Create an order summary table to store the general information - who, where, how much, and the status. Detail the ordered items in the order item table and link it back to the summary by ID.
• Use singular table and field names - each record only represents ONE thing.

Let me know if you have any questions. This may seem a little more detailed than you'd like - but it will save you headaches down the road.One other note, many (including me) tend to put all the address information (billing and shipping) in the order summary table. If the customer moves and you link back to the ID, then it will look like old orders were shipped and billed to an address the customer no longer lives at. So you could put each address field i the order summary twice (once for shipping and once for billing).

[vchris]

It's a bit confusing, so many tables and fields compared to what I came up with but I understand how it would be easier to work with. Here are some questions I have:What does oe stand for in the table name?How would prm_media be used? What is the use of cat_code?How would ord_cst_ID be used?How are the global fields used?Thanks, I appreciate the time you're putting into this.

[Skemcin]

It's a bit confusing, so many tables and fields compared to what I came up with but I understand how it would be easier to work with. Here are some questions I have:What does oe stand for in the table name?oe = order entry module - data centered around the ordering processHow would prm_media be used? Track any given image, video, sound bite you have for a product. Each record gets a new prm_ID but then those images, videos, sound bites are associated to the same product by the prm_prd_ID. Essentially give you the ability to have more than one multimedia file for each productWhat is the use of cat_code?You would use the code as a non human definition where the name might be something you display online. So lets say you are selling widgets in book, video and audio format. Your cat_code might be 'book', or 'video, or 'audio' while your cat_name might be 'Books & Publications', 'VHS & DVD', and 'Tape, CD, & Download' respectively. Internally, your system would use the cat_code for certain grouping criteria since there are no special characters by rule.How would ord_cst_ID be used?This is used to link the order to the customer. since the cst_ID is the unique identifier for the customer, you put that with each order (ord_ID) and you now know which orders are for which customers. Its a parent child relationship. Doing this will eliminate the need to create table aliases in your select statements - you essentially never have duplicate field names.How are the global fields used?What I mean by global fields is that you would add these fields to all your tables, replacing the 'xxx' with the respective table prefix. It allows you to generate reports on add and change dates/users. The delete flag allows you to soft delete items if you like.Thanks, I appreciate the time you're putting into this.

Did this help?

[vchris]

Ah much clearer but would you really need those global fields in all tables?

[Skemcin]

The idea behind those fields is to simply be able to consistently track database activity across all tables. At anytime you will always be able to tell when and who created a record. And since there are relational tables, the add dates (and change dates fro that matter) help troubleshoot complex insert, update, and delete processes. The only field I could see as not being really needed that much is the delete_flag - if you are going to hard delete everything, then the field serves no purpose. You could also do without the change user/date fields too if you're not gonna worry about that. In more object oriented programming, integrating these fields is not as annoying as regular/decentralized inline SQL statements.So, at a minimum, the add user/date should always be there. Closely behind that is the change user/date and then delete_flag the most expendable.

[vchris]

it's a little more complex than I first thought but I'm trying to understand your database structure. I just wanna clear a couple more things so I implement your tables.Is it missing cat_id in the product table?What is the use of the Customer to Address (cxa_customer_x_address) table, the association of a customer to his address could be done with a id field in the customer and address table.Why would I need 2 emails in the customer table?I wouldn't need the global fields in the cxa_customer_x_address table right?

[vchris]

I'll also need a users table for the admins to access the admin control panel.

[Skemcin]

it's a little more complex than I first thought but I'm trying to understand your database structure. I just wanna clear a couple more things so I implement your tables.Is it missing cat_id in the product table?What is the use of the Customer to Address (cxa_customer_x_address) table, the association of a customer to his address could be done with a id field in the customer and address table.Why would I need 2 emails in the customer table?I wouldn't need the global fields in the cxa_customer_x_address table right?

Sorry for the delay - I was out of town with a PC that Blue Screened on me the first of four nights away...

• Good catch on the first point here. You will need this table to link up the product to a category - this will allow a one to many relationship (a product can be put in more than one category) should you need it.Product to Category (oe_product_x_category)pxc_IDpxc_prd_IDpxc_cat_IDThat should do the trick.
• you will likely want to link to more than one address - this will permit that. In your check out process, you can link to an addres here as a shipping or billing address without worries that it will be overwritten but only if you soft delete records. If you add the ID to the customer table, then you will need to add one for shipping and one for billing and then add the complete billing and shipping address to any order summary so you can be certain your historical information is correct.
• I figured the introduction of cross tables _x_ was getting a little hard to follow or just out of hand - so I figured e-mail addresses didn't need to be normalized like the other stuff. So one could be primary and the other secondary - or label them as work and home but make sure you label one of them primary so the user knows that is where e-mail is getting sent.
• Global fields should go in all the tables. Depending on which ones you have selected to use, you've find huge benefits in the future by having these consistancies and these conventions. Even though it may not be on the list now, reporting on the data will be highly influenced by these fields. Plus, they are all auto populated fields, so its really not a pain to accommodate them. You could arguable use them all by auto filling, and never use them again - but at least you *could* if you ever wanted or needed to.

Let me know if you have more questions on this . . .

[vchris]

everything is going pretty smoothly. I had a question. Since this site will have both Canadian and US currency, how can I keep up to date with the current exchange rate? Is there a feed somewhere?What would be the best way to get this working properly?

[Synook]

Since this site will have both Canadian and US currency, how can I keep up to date with the current exchange rate?

I would suggest just storing the prices in US dollars, doing all the calculations in US dollars, and just outputting the relevant figures at the end converted.I can't find a feed for the US dollar, but I did find an XML file updated daily for the Euro - you could use thathttp://www.ecb.int/stats/eurofxref/eurofxref-daily.xmlOr you could regex the value out of the Google page:www.google.com.au/search?q=INSERT_US_DOLLARS_HERE+US+dollar+in+Canadian+Dollars

[vchris]

I'm trying to create this query to delete a category (update the delete flag) which involves updating a flag field in the category table and all the product with this category. This means 4 tables needs to be updated. My database design is in post #4. I realise Skemcin is probably the best person to answer this question.Here is my attempt:

$deleteCat = "UPDATE oe_product prd, oe_product_attribute pra, oe_product_category prc, oe_product_media prm, oe_product_x_category pxcSET prd.prd_delete_flag = 1, prc.cat_delete_flag = 1, pra.pra_delete_flag = 1, prm.prm_delete_flag = 1WHERE pxc.pxc_cat_id = ".$_SESSION['cat_id']."AND prd.prd_id = pxc.pxc_prd_idAND pra.pra_prd_id = pxc.pxc_prd_idAND prm.prm_prd_id = pxc.pxc_prd_idAND prc.cat_id = pxc.pxc_cat_id";

[Skemcin]

Hi.I'll start by pointing one thing out first. With this type of naming convention, you never need to use table aliases since you will never have a duplicate column. Its a huge time saver for development but also benefits performance.Having said that, I put this together, but its sort of hard without a database built to understand things should this fail:

UPDATE	oe_product_category,		oe_product_x_category,		oe_product,		oe_product_attribute,		oe_product_mediaSET	cat_delete_flag = 1,		pxc_delete_flag = 1,		prd_delete_flag = 1,		pra_delete_flag = 1,		prm_delete_flag = 1	WHERE	cat_id = ".$_SESSION['cat_id']."		AND pxc_cat_ID = cat_ID		AND prd_id = pxc_prd_id		AND prd_id = pxc_prd_id		AND pra_prd_id = pxc_prd_id		AND prm_prd_id = pxc_prd_id

Basically, I start from the category, then to the cross table, then the product and its related children. Let me know if this helps.Of course, I can't help but to ask why you'd want to do that. I mean it seems a rather aggressive task to carry out - getting rid of all the products in a given category . . . I hope there are a couple process confirmations ahead. But requirements are requirements.:)Let me know if that helps.

[vchris]

I would think that when deleting a category it should delete all products. That doesn't mean you can't then move those products to another category. I could probably have an option to move all products in category being deleted to a new category or something, I don't know. Do you have a better idea?

[Skemcin]

Sorry I didn't reply yesterday - wife and one kid decided to get sick on me so I was home from work tending to family.With this data model, you just have to remember that a product can be associated with more than one category. So when you delete a category, you really only need to flag the oe_product_category and oe_product_x_category tables - you should never really touch the product.Simplified:

UPDATE	oe_product_category,		oe_product_x_categorySET	cat_delete_flag = 1,		pxc_delete_flag = 1	WHERE	cat_id = ".$_SESSION['cat_id']."		AND pxc_cat_ID = ".$_SESSION['cat_id']."

In your application, I would have all my product select queries extending to include the category - and therefore looking at the delete flag (in both the cross and category tables). Any search result or display page would either be grouped by category or otherwise have some indication to what category something belongs to. Depending on the front end, you might have a page that could list the same product more than once - given this model. So, there you just have to pull distinct product IDs or sort and group the page by category so they do not appear next to one another.All in all, you should just flag the category record and the record(s) that associate it a product - nothing more.I'm guessing this is more complex than you might have originally gambled for - but that is the fun of development. Just think, you will have one slick little application when you're done - and a very versatile one at that.

[vchris]

How would I display this:category 1item 1item 2category 2item 1...Here's what I have:

$getAcc = "SELECT prd_id, prd_name_e, cat_name_eFROM oe_product, oe_product_category, oe_product_x_categoryWHERE prd_id = pxc_prd_idAND pxc_cat_id = cat_idGROUP BY cat_idORDER BY cat_name_e";

also:

$getAcc = "SELECT prd_id, prd_name_e, cat_name_eFROM oe_productJOIN oe_product_x_category ON prd_id = pxc_prd_idJOIN oe_product_category ON cat_id = pxc_cat_idGROUP BY cat_name_eORDER BY cat_name_e";

[Skemcin]

I don't use the SQL Group By function that often, but I think it is used to bunch items together:http://www.w3schools.com/sql/sql_groupby.aspI've always used ColdFusion's <cfoutput> to do the grouping:

<cfquery name="qryGetProductList" database="xxxxxx">SELECT prd_id, prd_name_e, cat_name_eFROM oe_productJOIN oe_product_x_category ON prd_id = pxc_prd_idJOIN oe_product_category ON cat_id = pxc_cat_idORDER BY cat_name_e<cfquery><cfoutput query="qryGetProductList" group="#cat_name_e#">#cat_name_e#<br /><cfoutput>#prd_name_e# <em>(#prd_id#)</em><br /></cfoutput></cfoutput>

I'm not sure if this helps, but maybe your PHP could do this?

[vchris]

Not sure if php can do this but I managed to do an if statement in my while loop. If the category name is the same as the previous category name then don't display it. Works but there is probably a better way to do this...

[Skemcin]

Not sure if php can do this but I managed to do an if statement in my while loop. If the category name is the same as the previous category name then don't display it. Works but there is probably a better way to do this...

maybe, but I can't see that the technique you're using is all that inefficient - not enough to keep you looking for more.

[vchris]

How would I then insert a new product? I know I have to do my insert in the products table first so I can then query for the new product id which I will then enter in the media and attribute table. But can I have a select statement in my insert statement?

[Skemcin]

Sorry (again) for the delay on the reply. I can't begin to tell you how crazy its been at work . . .You could collect all the information on one form. Then Run your first insert to create the product like you mention. In that statement you would use the function to get the last inserted ID. In SQL, it is @@identity and you can find more here:http://www.google.com/search?q=%22%40%40identity%22+sqlLet me know if this helps.Example:

BEGININSERT INTO	Books (		Title,		Price,		PublishDate )	VALUES (		@arg_Title,		@arg_Price,		@arg_PublishDate );	SELECT @arg_BookID = @@IDENTITY;END;

[vchris]

I'm almost seeing the end of this project but have 2 questions.In my oe_product_attribute table I added pra_special_flag, pra_special_start_date, pra_special_end_date. Then if I want to display products on special, I simple check the pra_special_flag. The problem is how can I have the pra_special_flag change to 0 when the pra_special_end_date is passed?skemcin: you know how I have the cu_address table with adr_line1 and adr_line2. Wouldn't it be better to have one field for each line information (street number, street name, apartment, province/state...)?

[Skemcin]

Chris,Glad to see you're almost done with the project - hope it is turning out well and even above any expectations. Here are the replies to the two questions you pose:

• The easiest way to address this is to write a query to a file and set it as a scheduled task to run every night. The query would look like:UPDATE oe_product_attribute tableSET pra_special_flag = 0WHERE pra_special_end_date < xxxxxwhere xxxx is a current date function for whatever way you do things (i.e. #now()#, GetDate(), Timestamp() for ColdFusion, MS SQL, MySQL, etc.)You can set Scheduled tasks a number of ways but your environment will typically determine/restrict how you set this up. When I do this, I wrap the query in a cftry and have errors emailed to me so I know when things are out of synch. I also tend to write a select statement of the same format just before that and log the count of records I am updating. This allos me to avoid extra processing for zero record scenarios and if I know there are a reasonable number of records each time, I might even write my own log with the date/time of the update along with a comma deliminited string of pra_key values so I can audit things.

• I've only had one project where I've either found it better or it was specified such that the street direction, name, number, and type were individual fields (123 NW Mystreet Avenue). It was in a property management tool where I knew the municipality had pretty consistant address formats. My concern implementing this on a larger scale is just managing the information and how it is presented on a mailing label and such - what is on line 1 vs line 2 when do you use line 3. In any case, you could always benefit from denormalizing the information in some respect.

Let me know if that helps.