Forum Moderators: buckworks
I'm just thinking about how I'm going to structure my new site which will be selling bikes and biking components (not a real site).
Is it the norm to have ALL the products in one databse table? If so, will it pose any problems if I use 2 tables? Basically, the main navigation will be split into different sections....
- The first section will be Bikes and there will be links by manufacturer under the Bikes heading. These links will go to a list of models and the model links will go to a list of bikes.
- All the other sections will be biking accessories and each link will go directly to the product listings.
Therefore, I guess I would need a table for Bikes and a table for all the Accessories.
Does this sound ok? Or is it much easier to work with a single product table?
Many thanks :)
At the other extreme you use a table for each different type of product. This allows you to customize each table to the product (bike fields: rake angle, downtube length, front derailer make etc... - helmet fields: mfr, model, name, weight, shell construction)
I personally like the split of the bikes from the accessories because it does allow for more detailed information on the products. The accessories could be broken up as well if you wanted to take advantage of detailed information on things like helmets (weight, safety ratings, shell construction) and lights (battery life, battery type, charge time, time to live).
It really comes down to how you will use the information you have, whether you foresee getting more detailed info and utilizing it in feature comparisons or just as product info, etc.. In all cases, think beyond what you have in your hand right now and look to what you as a shopper would want to see on the site and design for that. Think flexibility to modify the info and expandability of services offered.
I am unsure of how to proceed with storing bike specifications as they vary from one manufacturer to another and from one type of bike to another. So would it be best to have a seperate table for bike specs using the bike_id as a foreign key? It would consist of 4 columns - spec_id, bike_id, component (eg front mech), name (Shimano XT 9 Speed). However, that would lead to a few thousand rows.
Also, for the accessory details, would it be best to just have a text field for entering details? Or should they be split into separate columns?
What I'm more concerned about is how the database is used for the payment process. I'm really not sure about what tables are needed and how the database is used for the shopping cart and checkout process. Any ideas?
Many thanks :)
I am unsure of how to proceed with storing bike specifications as they vary from one manufacturer to another
I'd use one table with seperate fields for the stuff that's common across most of the bikes. Use NULL in the fields that don't apply and use a comments field(s) for stuff that is really only pertinent to one or two bikes.
Also, for the accessory details, would it be best to just have a text field for entering details? Or should they be split into separate columns?
Split what exactly?
What I'm more concerned about is how the database is used for the payment process. I'm really not sure about what tables are needed and how the database is used for the shopping cart and checkout process. Any ideas?
Orders and checkout are completely independent of the products with the exception of product ID, Quantity, and any product variables (like size, color). These should be stored in an orders table along with an order ID and buyer ID.
When I was talking about Accessory details, I was thinking that some products may have a quick sentence to describe it, whereas others might have a few bullet points. Despite the format of the details, would I be able to have a details field in the Accessories table? If so, would it be good practice to write HTML into the field in order to get the correct format?
With regards to a ShoppingCart and Orders table, would each product table have to have productID as the primary key, as apose to bikeID in one and accessoryID in the other etc.?
Finally, is it best to use sessions rather than cookies for the shopping cart?
Many thanks :)
You are asking questions about some very fundamental areas. The decisions you arrive at will dictate quite a bit about how you do things in the future.
Any individual product has several pieces of related data. Some data may be important to the customer, but not effect pricing. Other data might effect pricing.
Product A might only be available in black. Product B might be available in black, green, red, or white, and the color selection has no impact on pricing. Product C might be available in black, green, red, or white w/red pin striping. The white w/red pin striping option costs more than the other colors.
The user may have to choose a length for Product A, a length and width for Product B, and a weight and length for Product C.
The point I am trying to illustrate is that you can quickly paint yourself into a corner by hard-coding these options into the data model. It is better to extrapolate a generic model and work with that. One table for products. Another table for possible attributes. Another table for possible attribute choices. Another table to store selected attributes for specific products. And yet another table to store selected attribute choices for specific products.
So you add "Product A" to the product table. Add "Color" to the attributes table. Add "Black", "Green", "Red", "White", "White w/Red Pinstripes" to the attribute choices table. Associate Product A with Color in the product attributes table. Associate Black, Green, and White w/Red Pinstripes to the Product A - Color attribute in the product-attribute-choices table.
There are a lot of variances on this implementation. But if you have more than a handful of products AND those products do not have uniform attribute sets, you will find that something like this pays off in the long haul.
>> re: multiple table causing headaches for reporting.
I'm not sure what you're alluding to. I've created more granular reporting with more accurate information by splitting up data. Can you explain what you're thinking a bit more?
Access can't handle that in one quiry so we had to use stored procedures, that was something that we wanted to stay away from but it was required because we had the data separated into the 2 tables.
If I am put into the same situation again I would need much stronger arguments to split the data like that again because overall it added a lot of time when it came to all the reports that they needed.
<added>It would not have been as much trouble if a more powerful database was used, but I still probably would not do it unless there was more reason then we had. </added>
It would kill me to create a "litres" column in my table, so in my attributes table I would have one row, three entries for each water bottle:
ProdID
AttributeName
AttributeValue
Great for keeping the weirdo product normalized and clean.
Bill
Here are some reasons why you might consider storing the rest of the data in additional product tables:
- If you want to associate an accessory with 1 to many bikes.
- If you want to offer an item as a substitute in the event something is out of stock.
- You are planning on offering multiple price levels.
- You are planning on supporting multiple currencies.
- You planning on offering your bikes or accessories in various flavors (red, green , blue).
- Your planning on offering your bikes or accessories in various sizes (Helmets, Shoes, Bikes and clothing all have size attributes).
Personally, I would normalize the data model as suggested by lorax. It's a recommended practice that really helps your application scale.
It sounds like you have a really fun application ahead of you.. I'm kinda jealous.
I now have 60 tables in that database... it's to the point where reporting with MySQL is a pain, because it is so limited. Having normalized data is wonderful for reporting if you have a database that lets you do complex queries- MS-SQL, PostgreSQL, Oracle.
If you have access to it, Powerbuilder can be a fun tool for modelling your database.
ProductTable: PartNumber, Title, Description, Price, Department...
BikeInfo: PartNumber, NumberOfGears, SizeOfFramesAvailable, Colour, Weight...
AccessoryInfo: PartNumber, TypeOfAccessory, Weight, Size...
In this format, when you run the basket page, you only need to look up ProductTable (this will help with the speed of the basket page). For information pages about each individual product, if you look up PartNumber in BikeInfo and it does not exist, then it is not a bike, but if it exists in AccessoryInfo then it is an accessory. Depending on which table contains the PartNumber, will determine how you print the page to the screen.
My advice is you have to look at how big you are going to get. If you are going to have 50+ unique users at the same time or more than 1,000 products, I think you have to build a proper db for performance reasons.
Multi-tabled dbs are a huge pain for laymen. For example, until we built a complex vb tool, there were only a couple of us who could append products to the db. That's because, in our case, they had to go into the products, options, names, subcategory, category and class tables. It can get awfully frustrating if you have to write a query every time you want to check today's sales or add an item.
I built a very small website for my dad, and just kinda slopped the db out there - it's not like his site is gonna go down 'cause I failed to properly index his 11 rows of data :)
On the other hand, me and another coder in our shop will sometimes spend days on the initial layout of a database for a large ecomm site.
Bill
I have a much clearer idea of how I need to proceed now. I'm going to knock up a rough idea of an ERD and if its ok I will post back to see what you think :)
I'm a little unsure about all the tables asscociated with attributes, but this is how I understand it at the mo......
I have a single product table which will be something like this:
PRODUCTS TABLE
-productID
-categoryID (this is for accessories which are 1 link deep)
-subcategoryID (this is for bikes that are 2 links deep and have a subcategory between product and category. I will use an if statement to check if a subcategory value exists)
-bike (a this value is equal to 1 then it indicates that the product is a bike and the specification will be displayed)
-price
-sale price (if a sale price exists then the product will not be displayed but instead will be displayed on the sales page)
-offer price (if the item has an offer price then it will be displayed instead of the normal price)
-homepage (if this value is equal to 1 then the product will also be displayed on the homepage)
-description (a general description for accessories)
-thumbnail_path
-picture_path
Does that sound ok? Or is it getting a little messy?
I have a separate table for manufacturer names instead of writing into every product name.
And here is what I think I need for attributes:
ATTRIBUTES TABLE
-attributeID
-attribute_name (eg colour, size)
ATTRIBUTE CHOICES TABLE
-attribute_choiceID
-attributeID (foreign key)
-productID (foreign key)
-attribute_value (eg, red, green, blue, small, medium, large etc)
So where do I go from here? Is it best to use drop down lists next to each product and store attribute_choice choices in another table and use the primary key from this table in the shoppingCart along with the productID?
Or is the extra table unnecessary if I have a separate add to basket link for every choice that is available? An example is on this page:
snipped
Am I right in thinking that the extra attribute_choice choices table would not be needed if the above system is used? Or should I go for the drop down list anyway. Does that make sense?
Any help would be greatly appreciated. Many thanks :)
[edited by: DaveAtIFG at 5:54 am (utc) on July 3, 2004]
[edit reason] Deleted URL [/edit]
ID_Name______________Parent_id
-----------------------------------
1__Bikes_____________0
2__Mountain Bikes____1
3__Full-Suspension___2
4__Hardtail__________2
5__Bike Accessories__0
6__Clothing__________0
7__Cycle Gloves______6
You get the idea!
Also, there is no need for the bike flag since you can just group them all under the bikes category. Also this keeps a bit more generic, should you wish to re-use this for another application.
One thing that you might want to add to the product table is a manufacturer_id which links to a manufacturer table.
You attribute tables sound spot-on, you really don't want to split up the products table.
- the complexity of your code
- the ease/difficulty of administering your products
- the granularity of the reports you can get
There are other areas to be sure, but those are the primary ones (off the top of my head anyway).
If you are going to be entering a lot of products, then you want to make it easy on the administrator to enter that data. It is easy to enter a product if you check off a few attributes on a list: color-yes, length-no, apparel size-yes, etc. Then for each of those, you also select from a list of available choices: red-yes, green-yes, magentta-no, etc.
The alternative is that you repeatedly type in those attribute names and/or the attribute choices.
The bottom line is that keeping track of non-homgenous products takes effort and time. You can put in the effort and time up front in the design and coding. Or you can put it in later during the data entry. Doing it up front gives you a scalable solution. Doing it during the data entry gets you on-line quicker.
It may also be advantageous to break your category-product relationship out into separate tables:
Product table
CategoryProduct table
Category table
This allows one product to belong to multiple categories. That in turn provides you with a way of helping visitors find products where they expect them to be. Seat covers can go under "Accessories" and also under "Seats."
Scalable, flexible, granular all equate to more time up front as has been noted. But the benefits are many. There is a point of diminished returns though - watch out for it. Less complex db designs are definately easier to get up and running but lack the ability to display and analyze data in a detailed fashion.
Where do you draw the line? That's up to you! ;)
Here is what I have done so far:
snipped
What do you think? Am I on the right track? I know there is a few things missing, but I'm not sure what to add just uet. Any ideas?
Thanks again :)
[edited by: DaveAtIFG at 5:55 am (utc) on July 3, 2004]
[edit reason] URL snipped [/edit]
Also not sure makeid should be in category table, at first glance looks like it shouldnt but you would know.
Are categories only one level deep, or will you have sub-categories? If a product can belong to more than one category, it should have a separate table.
Can a product have many attributes of different types, each combination represented by an SKU? (Think a sweater with different sizes and colors: each has its own ID... does the same thing ever apply to bicycles?) Will these vary in price?
Is there only one currency? If not, is there one primary currency with the rest based on exchange rates, or are the prices determined by an administrator?
cartId/cartItemId is probably one too many. If there is only one attribute to choose, it should be in the cart; if there isn't always one or sometimes many, it should be in a different table.
So... really, a lot of this depends on how simple/complicated you want things :)
I wish most of the developers I have worked with took the time to think a project out like this.
See ya,
Bill
danieljean, the 2 link from Product to Category were a mistake (the same relationship), thanks for pointing that out. All the bikes will be 2 levels deep so I had originally planned to have a SubCategory table, however I had implemented a ParentID column in the Category table based on a recommendation by jweighell on page 2 of this topic. So an example of how this might look is here:
snipped
Do you think that will work ok in practice?
Yes, bikes will have many attributes of different types. For example, a bike could be available in red, green and blue and each colour variation will be available in different sizes eg 15", 17" and 18". This is what I am unsure of how to model. How will the persons selection of attributes be stored in the database/ShoppingCart?
bsterz, many thanks for your words of encouragement, much appreciated :)
Right, so I've made some minor changes, but I'm not sure about a couple of things, as mentioned above. Any ideas?
Many thanks for all your time :)
[edited by: DaveAtIFG at 5:56 am (utc) on July 3, 2004]
[edit reason] URL removal [/edit]
Then in product table i create a range coulmn with a relation ship to a ranges table.
Then say a bike comes in 4 colors, when a user visits the product page it looks at the range column and pulls in all items from the same range.
In my case all the different coloured T shirts. then depending on which one the user ordered would effect which product was added to the cart.
That was my approach
A parentId is great to have in the category table. Since I read in another thread you were doing this as an MSc project, I'll just tell you to look at the composite pattern, and keeping it as a singleton in your application scope :P Seriously though, it's handy if you have example.com/category/subcat1/subcat2/ to traverse a menu tree object three times for each of those categories, then asking for its children. Or you can output it in nested lists on the client side with a very generic loop, remembering only to output children if the category is selected.
The way OSCommerce has it set up is similar to what kris describes, and what I adapted. An article can have various "attribute types", to each correspond one or more "attribute values." An attribute table holds the type/value pair and the price differential is in another table, with one value for each currency.
If there is only 1 attribute (type/value combination), then it is a fixed value for the product, otherwise the customer has to choose. Yet another table holds the SKUs, by productId / attribute combination.
Not to mention that all this is multilingual... the queries were rather mind-boggling, which only highlighted the need for using good naming conventions and descriptive titles. (Note: I was using MySQL, which is a stupid thing to do with a db of that complexity. If you have views, you'll have a lot less trouble)
Errr... well, that was far longer than I was planning... :)
***************
Can I just ask why the URL's I posted have been removed? Have I overlooked a particuler rule?
***************
For modelling I'm using SQl Server 2000 and Visio.
I'm going to do some more work now so I will take your suggestions into consideration and see what I can come up with.
Many thanks :)
