[eluser]packetfox[/eluser]
Hello all,
i am developing a Shopping Cart using CodeIgniter and its new Cart Class. Somehow i need to come up with the ability to assign different attributes to a Product, which may influence the price.
An example:
A User wants to buy a mobile-phone, which comes in two colors, and has two different memory sizes; 8 or 16GB. Picking a different Color does not result in a price change, but choosing 16GB instead of 8GB adds 100$ to the purchase Price.
My problems are as follows:
1. Figure out a sound database design that allows me to setup different Attributes and their Values; then associate them to a Product.
2. Come up with a SELECT Query that pulls in all attributes info assigned to a certain product so i can display the product choices nicely on a product page. Ive been trying different methods for a while, but cannot come up with the correct way to join up all my tables.
3. Calculate the new Order total, when an attribute had a price value.
I would be very grateful if anyone can share suggestions or ideas, maybe even correct my database design and share some code that can help facilitate what id like to do.
Here is what i have so far, for examples sake limited to needed fields only:
Code:
// My Products table
CREATE TABLE product (
id bigint(20) unsigned NOT NULL auto_increment,
name VARCHAR(200) NOT NULL,
price DECIMAL(14,2) NOT NULL default '0.00'
)
// My attribute table, holds things like
// color,size,memory
CREATE TABLE attribute (
id bigint(20) unsigned NOT NULL auto_increment,
name VARCHAR(200) NOT NULL
)
// attribute_value table, holds values related to
// attributes, so for color it would hold red,green,blue etc
CREATE TABLE attribute_value (
id bigint(20) unsigned NOT NULL auto_increment,
attribute_id INT,
value VARCHAR(200) NOT NULL
)
// associates attribute values to products
CREATE TABLE product_attribute (
product_id INT,
attribute_value_id INT,
cost DECIMAL(9,2) default '0.00'
)
