MySQL Database Structure Question

[Guest]

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.

[bnbwalt]

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

[bregent]

>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.

[Guest]

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....

[bregent]

>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.

[Guest]

OK, I see what you mean.  As far as subcategories go, I was just thinking like Lighting Fixtures -> Outdoor / Indoor  or wall / ceiling, but I see what you mean.  The inventory thing should be compatible, that's just a couple extra fields for on hand and on order, and set up an extra function to subtract from the on hand for each sale.  I'll just add those in right now and leave them with a null option until we're set up for that.  Thanks!

I know it would be some extra work, do you think I should just do seperate tables for each category?  Fans, Lights, etc, and do the same for the images?  That's some extra work, but I'm not against that if that's what it takes to make it work.

I don't suppose I could build the table set up using Access and just import that as a blank template?  It's easier when I know I'm not tied into anything while I'm doing it.  I've seen the import option, but am not completely sure about the compatibility.  Addionally, using the query across multiple tables to select (say) all of the images from that table where their product is the same as a product ID in the other table, is that known to have any severe problems in PHP?  Using access just for simple set up theory and to get a better look at ER Diagrams and noticed that little flaw.

[Guest]

Ok, even more further addendum.  As stated, I'm using PHPMyAdmin 3.2.0.1.  I can build the tables and add the info into them, how am I supposed to edit the relationships to include foreign keys?  Their FAQ really only addresses this for even older versions using features that no longer appear to exist.  I've tried all of the tabs, but can't find it.

[bregent]

>I know it would be some extra work, do you think

>I should just do seperate tables for each category?

No, not that way. There are 4 basic ways to constuct this.

1) Build a product table to hold all possible product attributes. The problem with this is that each product ends up with lots of unpopulated columns for attributes that are not appropriate. I don't recommend this.

2) Use different tables for each product category. This makes it difficult to build queries that span categories. Again, not recommended

3) Use a master product table to only store attributes that are common to all categories. Then use subtables to store attributes specific to each category. Again this makes it difficult to build queries because you need to alter your joins based on the category. But, you are able to easily query the master product table.

4) Similar to 3 above, but instead of subtables, you build a generic attribute table. Search the web for entity-attribute-value model for more details.

>I don't suppose I could build the table set up using

>Access and just import that as a blank template?

You could model it in Access if you don't have any other modeling tools. Access does not have built in functionality to export ddl - but I believe there are add-ins.

>Addionally, using the query across multiple tables to

>select (say) all  of the images from that table where their

>product is the same as a  product ID in the other table,

>is that known to have any severe problems  in PHP?

You lost me. Why would you have the same data in multiple tables?

[Guest]

The same data thing would be like a fan's ID number being CF700 (primary key in that table), after that we'd have the color code like dbk for black or something.  I was thinking have the Image table with the primary key being the image path, and the "Product" field as a foreign key having CF700  to make them easier to line up to be pulled at the same time, that way all of the images could be pulled for those fan colors at once.  That's all I could think of to make sure that the images could be made to pull up more than one on the same product without having more fields in the table.  I do have the PHPMyAdmin, but it's not letting me model the entity relationships in any way shape or form, so I figured since Access has that built in and easy to use, it would be worth a shot.

And a double thanks on that last bit, I knew there was a term for the entity attribute model, but for the life of me could not remember what it was.  Been searching for database diagrams and hoping for the best....

[Guest]

Ok, possibly the dumbest question of all time folks, I kid you not and I'm sorry if this is the wrong area to post it in.  I've got a lot of training and practice with Visual Studio 2010, and I'm really good with C#, which has a built in feature to create, edit, and update a MySQL database.  I've done some searches but you know how search engines are when it comes down to very specific questions.  I could build a small easy-to-use program in C# for the sole purpose of building a database and filling it without all the tedious issues that PHPMyAdmin has.  Would I be able to import that to PHPMyAdmin 3.2.0.1?  I know that would give me the ease of use I'm looking for and it could also have a little built in feature to double check my refrential integrity and point out redundant data if that happens.  Would VS be able to save in a format that I could upload without losing the ER settings or data?  If this is going to end up being a popular add on I'm gonna lose it lol.

[David_Powers]

psifreak wrote:

I could build a small easy-to-use program in C# for the sole purpose of building a database and filling it without all the tedious issues that PHPMyAdmin has.  Would I be able to import that to PHPMyAdmin 3.2.0.1?

phpMyAdmin is simply a web-based front end for MySQL. If you create your own program to build the database and populate it with data, your job is already done. There's nothing to import. Your data will already be in MySQL.

When testing for my books, I use a combination of phpMyAdmin, Navicat, and SQLyog (all front ends for MySQL). There's only one underlying database. Any changes to data caused by insertions, updates, or deletions in one front end affect what I see in the others.

If you're talking about transferring from one database to another with phpMyAdmin, you can do so using a dump file (use the Export tab in phpMyAdmin), and then importing it through the Import tab. For technical reasons, phpMyAdmin cannot handle imports from dump files that exceed 2MB. That restriction doesn't apply to Navicat or SQLyog.