Suggestions for MySQL database layout for online shop

[fatshady]

I am gonna start writing an online shop for a new business I am starting, building and supplying computers and components. I'm doing it using a PHP/MySQL base. I need some advice on the SQL layout. I am stuck between two options.

One is to create one huge table, with all components and details of each of them, but there is going to be a lot of empty columns (infact most will be empty on most rows)

The other is to use many smaller tables - which means none of them will have empty values, but means that the database defines the logic rather than PHP - which I have been told is a bad thing.

My current website (see sig) uses sql and php quite well, and the coding is quite slick (so I've been told by someone whose been using it a lot longer than me).

Can I have some suggestions for the best way of doing it plz..?

[andycrofts]

Database design's not trivial.

Why don't you have a look at some Open Source project documentation?
www.oscommerce.com springs immediately to mind...

In fact, why don't you just use it, and save yourself months in the House of Pain?

-Andy

<EDIT> ..and if that's going to be a commercial site, for goodness sake check the grammar! Get your "You're" and "Your" sorted! And proofread before publishing. :p
Stupid child! :D
</EDIT>

[fatshady]

That is an option, but I'm quite up for the challenge to see it i can do it...

I guess if i get annoyed with it then I might download something and hack it here and there so it does exactly what I want...

[mrBen]

Personally, I would always use seperate tables - it's easier for reporting.

Something like, tables for:

Customer details
Product Details
Stock Details
Transaction Details

You then relate details across the different tables, and you present details from all tables to the customer.