Database Schema Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
koolsamule
Forum Contributor
Posts: 130
Joined: Fri Sep 25, 2009 10:03 am

Database Schema Question

Post 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!
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Database Schema Question

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Database Schema Question

Post by Benjamin »

No need to prefix tables with tbl_. We all know what tables are ;)
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Database Schema Question

Post by JakeJ »

I bet he learned databases from Access. That's part of the recommended object naming scheme. A tough habit to break.
minorDemocritus
Forum Commoner
Posts: 96
Joined: Thu Apr 01, 2010 7:28 pm
Location: Chicagoland, IL, USA

Re: Database Schema Question

Post 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.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: Database Schema Question

Post by JakeJ »

And there's certainly nothing wrong with it if it makes you more comfortable.
Post Reply