Page 1 of 1

Database Schema Question

Posted: Thu Apr 01, 2010 3:10 pm
by koolsamule
Hi Chaps,

I'm starting to build a small online clothing shop for my friend.

I've sorted out the basic database schema:

All items will cost the same, regardless of size.

tbl_cust - [custid] (customer details)
tbl_cat - [catid] (denim, floral, leather)
tbl_sub_cat - ([subcatid] (skirt, dress, top)
tbl_size - [sizeid, subcatid] (S, M, L)
tbl_product - [prodid, catid, subcatid, sizeid] (skirt product, dress product, top product)
tbl_shop - [shopid, prodid] (quantity, price]
tbl_sales - [saleid, prodid, custid] (size, quantity, price, sale_date)
tbl_sale_item - [saleitemid, saleid . . . . .


and that's when I get a bit confused . . . I need help sorting our the orders, quantities, customer etc. . . .

Any help would be most awesome!

Re: Database Schema Question

Posted: Fri Apr 02, 2010 12:44 am
by JakeJ
My best advice would be for you to install OS Commerce and poke around in that. That will teach you way more than we could here going piece by piece.

Re: Database Schema Question

Posted: Fri Apr 02, 2010 12:53 am
by Benjamin
No need to prefix tables with tbl_. We all know what tables are ;)

Re: Database Schema Question

Posted: Fri Apr 02, 2010 2:26 am
by JakeJ
I bet he learned databases from Access. That's part of the recommended object naming scheme. A tough habit to break.

Re: Database Schema Question

Posted: Thu Apr 08, 2010 5:25 pm
by minorDemocritus
If you're new to SQL, tbl_ prefix does make it a bit easier to spot the table part in an SQL query, though.

Re: Database Schema Question

Posted: Thu Apr 08, 2010 8:13 pm
by JakeJ
And there's certainly nothing wrong with it if it makes you more comfortable.