Skip to main content
A
Anonymous
November 20, 2010
Question

MySQL Database Structure Question

  • November 20, 2010
  • 2 replies
  • 1137 views

This is probably going to seem a little odd, but I need some outside opinions.  I'm using Dreamweaver CS5 to build a business website.  When all is said and done, it's going to have around 20,000 items (not anytime soon, but it'll get there).  I'm using a dynamic data set up to load the inventory because I'm way too lazy to build that many pages and links one at a time.

The corporate chain has two sections, lighting, and electrical supply.  I want the database to hold the product ID(of course), name, description, image(this is part of where the question comes in), price, sale price(discounts not our cost), an enum field for whether or not the item is discontinued(for our own records), size / weight for shipping reasons, and if reality is permitting, a field for color selection of the products.

My original idea was to have two tables, one for lighting, one for electrical.  Somebody pointed out to me that this could fail because they pointed out that some of the items might have multiple images, which is true.  Some of the items (like some of the fans we sell) have different colors but still cost the same.  They suggested I set the images up in a seperate table and use a foreign key. but that changes my entire structure.

Does anyone mind offering ideas on how I might set that up to avoid data redundancy and other problems that might pop up due to repeat listings?  I've got the images all with unique names (in the case of the same product in different colors, I added a color code like pb for polished brass, etc.)  That person had an excellent point on multiple tables, so now I'm wondering about a better and more reliable structure.

This topic has been closed for replies.

2 replies

B
bregent
Participating Frequently
November 20, 2010

>They suggested I set the images up in a seperate

>table and use a foreign key. but that changes my

>entire structure.

Then I suspect your structure is going to be changing.  I would expect a project like this to have no less than 10 tables,  maybe as many as 50 or more - it all depends on what the requirements  are. Do you have a solid requirements document put together? Do not make the common mistake of starting a project without one.

If you don't have much experience with database design then it's time to start learning. You will need to get smart with database design concepts, SQL, normalization, etc before you even start thinking about building an ecomerce site for 20K products plus their variations. How experienced are you with PHP?  Seriously think about starting with a smaller project, or at least work through some tutorials.

A
Anonymous
November 20, 2010

I'm ok with PHP, and usually pretty good with databases.  Right now I've got enough sample stuff to build a smaller scale prototype with about 200 products, from both lighting and electric.  Believe me, I haven't really done much more than start with the layout, because the many to many relationships just didn't occur to me.  Pretty much going solo on this project with occasional advice from one co-worker who's good at his job but extremely busy, so I think I just got a little to close to it and overlooked some clear and obvious problems.  As far as requirements, I'm using a 3 column div tag layout, the left column being the nav bar with the product categories.  When they click one of those, it should display that category (image, name, price) with the clickable name and thumbnail image in the middle, and from there they click the product they want to see and it'll display the full size image and full description on the right column.

Set requirements...wish I had a clear list, but the bosses are pretty much illiterate in this area of computers, so I'm on my own with that.  That's all I'm hoping to get out of the database is that three column layout.  I figure I'll make a changeable nav bar later so customers can switch from one store to the other and the nav bar goes from there.

We're hoping to get the database to have the fields I mentioned for now (at least for initial testing) and maybe add an inventory tracking function so we know how many we have throughout the chain.  We're not worried about that part now, just getting them up and selling cuz our current inventory system works just fine.  How would 10 or more tables be better?  Just breaking down the amount of products to be searched at a time, or is that how detailed you think it should be built?  Don't worry, I wasn't hanging everything on a two table set up, I knew there would be room for much improvement.

Current basic layout:

Lighting / Electric Tables          Image table:  ID (either the product ID without the color code or maybe the document path), category(lighting / electric),                                                                   subcategory (fan, lamp, etc)

id
class
category
description
price
sale price
discontinued
size
weight

That's what I've got so far, sorry for the weird way that list pasted....

B
bregent
Participating Frequently
November 20, 2010

>maybe add an inventory tracking function

>so we know how many we have throughout

>the chain.  We're not worried about that part now

If it's something you're planning to implement later, then you need to worry about it now. You don't need to design it, but you need to ensure that your current design will be compatible with it.

>How would 10 or more tables be better?

To start with, you've got categories, subcategories and classes. Those should be maintained in their own tables to be used for picklists. Might a product fit into more than one class or category? They usually do. Then you'll need move those fields to a many-to-many relationship. The same for size, color, etc.

B
bnbwalt
Participating Frequently
November 20, 2010

You are dealing with a situation where you have some one to many relationships.  That requires a table to hold the many.  In this case, product ID can have many colors or styles (images).  To easily resolve that, you need to need to have the image references, I would not recommend storing the images in the database, in a separate table linked by a foreign key.

Product table(s), you might even be able to keep both the electrical and lighting in a single table by having an additional field that specifies if its electrical or lighting, product id would appear as a foreign key in an images table.  The images table would be structured like imageid (PK), productid (FK), imageref (text field that contained the URL to the image).

This would work as long as there is not a case where one image could apply to more than one product.  If that is the case, you have a many to many relationship that requires an additional table that would map productid to imageid and imageid to productid.

Hope this helps

Walter

B & B Photography

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices