Database Structure Help, Custom PC shopping cart Database problem?

[bri0987]

I'm working on making a shopping cart using Php and MySQL. Building a Custom PC shopping cart...
I'm getting the pages put together and putting info into the database AND KNOW I have been hit with a major problem. (I provided an image below so you can see the structure)...

When I start pulling components into the pages, I will get every component for every product...

What I need is... Some components for certain PC products. So some PC products may have the same components as other products, but it may only have a few.

I'm stuck with a many to many relationship here. I cant figure other how to fix it, or maybe put something in between them to correct it. (I could assign each component to it's own PC product, but if that component is for ALL of the PC products, I will have to insert that SAME data 5 to 10 times AND then it would be a one to one relationship).

This is so frustrating. Can anyone help me figure this out. Or please give me some pointers.

VIEW THE IMAGE HERE: db_structure.gif

[Piranha]

Instead of saying "do this to solve the problem" I will post a link to a page that explains how to design databases. If you still have problems after you have read that article don't hesitate to post again with your new database structure.

[bri0987]

Piranha ... Thanks for the reply and the link. I have been to the link you have posted... I have pretty good with databases already. But this Custom Configuation PC shopping cart has threw a loop at me...

I posted an image that I drew in Photoshop of how the database is currently working... Did you see it? I'm keeping it simple for now. My question was how can I fit my database or rewrite it to make is work.

I have many components and many products.

How do I make it so some components are attached to some products.

(The way I need it to work)
Product number 1 -needs- Component A and B
Product number 2 -needs- Component A and C
Product number 3 -needs- Component A, B, and C

(The way it currently works)
Right now my Products look like this:
Product number 1: Component A,B,C
Product number 2: Component A,B,C
Product number 3: Component A,B,C

or if I wanted it would look like this:
Product number 1: Component A
Product number 1: Component B
Product number 1: Component C

[troybtj]

I'd lean towards this:

or if I wanted it would look like this:
Product number 1: Component A
Product number 1: Component B
Product number 1: Component C

Looking at your DB Schema, products should have a 1-many relationship with components, and components should have a 1-many relationship to technologies, and types (respectively).

Once it is set up that way (make sure you make the link/search key the Primary index), you can use a foreach() loop to build up the list of what all belongs in Product #1.

Otherwise, if you have a semi-standardized PC "out of box", trying to add in the additional config of the upgraded video card would be a nightmare. This way, you simply join the tables together on ID keys (HDD, Video, etc), and do a large join to get what you need.

--ETA: For your overall table, where you have the predefined systems, break it out into fields of "Product Name", "CaseID", "MBID", "MemID","HDD1ID", "HDD2ID", "HDD3ID", "HDD4ID", "HDD5ID", "RAIDID","AudioAcc", "Keyboard","Mouse","Disc unit 1", "Disc Unit 2", "DLT_1", "SCSI_ID", "DLT_2","Water cooling system", "CPU", ... etc ...

Then all your systems would be "built" in that "parent" database, each with a 1-many option for each serial number of the machine built. Avoid many-many links, and make sure the key you join tables on for the SINGLE QUERY that will give all the info about that system ID.

If I jumped ahead of myself, I'd suggest making the leftmost table into a "serial#" table, and having the description as a field of that table. Then add components in columns, of course, with the choices above, you will have NULL values in the table, but can get all the info in a single query to display.

[Piranha]

Sorry bri, I missed that you had components that you needed to have, thought it was only a many-to-many relationship. It sure makes things a bit more difficult.

I have to correct you troybtj. That is a bad way to create a database. What happens when there comes new kind of devices? With your way of doing it you have to change the database and the program. It is much better to design it to handle such things at once.

The solution is to use a many-to-many relationship between products and components. Then you can assign as many or as few components as you want with a product. The logic part of it should be in the application anyway, the database should only give you means to handle the logic.

But it is a problem that a product needs a component. You need to specify it a little better for me to be able to help you. Does the product "computer" need an "AMD Athlon 64 3800+, Socket AM2, 2.4GHz, 512kB L2 Cache, 45W, Boxed with fan"? Or does it need a processor? Or maybe an AMD processor? Or an AMD processor with socket AM2? There is a big difference.

You also need to specify (no matter if it is a component or a group of components) if it needs a component, if it is possible to use it or if it is impossible. You also should mark how many it needs and how many is possible to use. For example it would be bad if you can buy a computer with a motherboard for 1 CPU but buy 2 CPUs.

[bri0987]

Each product will have many different processors and motherboards and RAM to choose from.

When someone goes to the site they will be picking from a list for each component:

PC PRODUCT ONE ....
Processor:
o Intel Quad-Core 2.4 etc.
o Intel Quad-Core 2.6 etc.
o etc.

Motherboard:
o Asus Intel LGA 775 etc.
o eVGA ATX 1066mhz etc.
o etc.

etc.
etc.
etc.

A good example of what the shopping cart will function like is at Alienware.com
just go there and click onto a product and see how you can choose different components.

.......................

Also could I just make a relational table in between Products and components in the database. What I mean is. Could I make a table called: tbl_Relations
In that Table it would look like this:

Product_ID: 22
Component_ID: 1

Product_ID: 22
Component_ID: 2

Product_ID: 22
Component_ID: 3

Product_ID: 22
Component_ID: 4

Product_ID: 17
Component_ID: 2

Product_ID: 17
Component_ID: 3

Product_ID: 17
Component_ID: 5

I think that might do it... what do you think

[troybtj]

Piranha wrote:

I have to correct you troybtj. That is a bad way to create a database. What happens when there comes new kind of devices? With your way of doing it you have to change the database and the program. It is much better to design it to handle such things at once.

You are correct, my bad. I explained what not to do, then in the edit, did what I said not to do. Rough Night it was. :queasy:

[bri0987]

I have this already:

<p>Match this Component with 1 or more Products.</p>
	  <?php 
// 3. Perform database query
mysql_select_db($database_DM_database, $DM_database);
$db_result1 = mysql_query("SELECT tblproducts.Product_ID, tblproducts.Product_Name FROM tblproducts", $DM_database);
if (!$db_result1) {
	die("Database query failed: " . mysql_error());
} ?>

  <p>Content

  </p>
  <form name="form1" method="post" action="">

<p>
  <?php // 4. Use database retured data

while ($row = mysql_fetch_array($db_result1)) {
	echo "<input type=\"checkbox\" name=\"" . $row["Product_ID"] . "\" value=\"checkbox\" />" . " " . $row["Product_Name"] . " <br />" ;
}
?>
      <?php if (isset($_GET['Component_ID'])) {
  $Component_ID = (get_magic_quotes_gpc()) ? $_GET['Component_ID'] : addslashes($_GET['Component_ID']);
} ?>
     <input type="hidden" name="Component_ID" value="<?php if (isset($Component_ID)) { echo $Component_ID; }?>" />
    </p>
    <p>
      <input type="submit" value="Submit" />
    </p>
  </form>

How do I make it submit into the database more then one field.
For example: Currently there are 4 products. If I select all 4 of the products, I need PHP to insert 4 different times into the database
Looking like this:

Product_ID ....... Component_ID
1 ..................... 1
2 ..................... 1
3 ..................... 1
4 ..................... 1

What does the php code for that look like, how can I write this php...

....