database structure suggestions

mdojet

Hello Everyone,
I am building a site for a friend who is selling prepackaged school supplies. Each school, for each grade level has its own specific list of school supplies required.
My client will need to enter, into the database, the school, school district#, grade levels, and a supply list for each grade level. I was wondering what it the best way to tackle this. Mulitple tables?

My client also wants to give her customers the option to purchase only certain items from that supply list if they already have some of them. I have done a few websites with databases and PHP but this one seems complicated than my others.

thanks

Mike

mourisj

Of course, you might need a couple of tables for this (tere is more than one way to do). I did not work this out in detail, but at first glance you might need something like:

schools
grades
school_grades
packages
items
package_items
school_grades_packages
customers
orders
order_items

(each represents a table; A_B represents a table linking data from table A to table B n:m relationships)

There are ways to do with less tables, but you risk to have redundancy on some data.

And I did not yet consider managing the number of available items in the store, order fom the shop to the factory, etc.

Good luck ...

JJ Mouris

Roger_Ramjet

Mdojet, the fact that you have to ask "..multiple tables?" shows that you have not studied relational database theory at all. Best thing to do is to go to the library and get out a book on the subject. You need to know how to 'normalise' the data to produce the database design, ie what tables you need and what fields go in each. I have not found a good web tutorial on this or I would give you a link.
Don't go for some 'DB in 5 minutes' job, or even 'Access for Dummies'. There are an enourmous number of crap publications out there cashing in on the current explosion in db driven web sites.
Get one on realtional db that is at least 10 years old, the thoery has not changed so it can not be out of date. Being that old means that it predates the current fad and is likely to be solid on the theory. The relational theory is also independant of any particular brand of db engine.
Relational theory and data normalisation are infact one of the most straight forward things in the world of computing. Once you have the hang of it then you will find that any and every db engine are at your command. Likewise SQL. Once you know it then you know it for every relational database engine in existance. It is not knowing the theory that results in most of the problems people post here.

mourisj

Roger,

laserlight

You can start with a few of PHPBuilder's own articles:

Database Normalization And Design Techniques
SQL Theory & How-To