newbie question : Database structure

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I am getting up to speed with mysql which is all new to me. The "Teach
yourself in 24 hours" book worked a treat for syntax, and mechanics, but it
hasnt helped me  understand how to effectivelyt structure a database.

I have a table for each of the following categories :

    Customers Names
    Customer Shipping Address

I now want to create a table of orders (from which I can generate all
invoices etc). As each order will consist of (at least) a customer, a
shipping address, any any number of any products.

How best to store the products require for each order as it is open ended?

I had planned on creating an "order Item" which contains an FK for the order
to which the item belongs, an FK for the product that is required, and an
quantity. That table would have one entry for each item ordered, and could
be query'd by OrderNumber to build a list of qty/product for that order

It seems a little clunky, but is that how things like this are done?

Alternatively I wan thinking about a associative array as a field in the
Order table that would contain Product=>Qty pairs.

I think I am more or less there for building simple apps once I figure how
best to do this "undefined number of elements for field" type thing.



Any sugegstions


Re: newbie question : Database structure

OP: SOrry, I handt set my ID and email up before posting this...


Quoted text here. Click to load it

Re: newbie question : Database structure wrote:
Quoted text here. Click to load it

That's exactly how this is best done.

Quoted text here. Click to load it

This is going to be a lot more trouble.  Consider how you would validate
your data to answer the following questions:
- Are all product ID's in the associative arrays referencing legitimate
records in the product table?
- Are all the qty values valid nonnegative integers?
- How many orders contain line items with qty greater than 100?
- What's the average number of line items over all orders?
- Are there any invalid strings in the column for the associate array?
How often do you need to check for this?  What do you do if you find one?

These questions are easy to answer using simple SQL queries, if you use
the extra OrderItems table, but very complicated and expensive to answer
if you use the associative array design.

Bill K.

Re: newbie question : Database structure

Quoted text here. Click to load it

Thanks for this Bill...

It is always pleasing when others confirm your ideas when you are new to
something. The extra table has been my preferred way of doing this, the
associative array was just my attempt to think of other ways it might be

 I guess the clunkyness i was referring to is only that this will lead to a
huge table of ordered items, and that the order will have to be built up out
of querying this database. Then again, I guess that's exactly what MySQL
server is designed to do.


Re: newbie question : Database structure

Chris wrote:
Quoted text here. Click to load it

Exactly!  I don't think of the line-items table as huge, I think of it
as just the right size to store the data required.  :-)

Also, there are other forms of efficiency besides space efficiency.  The
examples I gave of how the associative-array solution would be difficult
to program make that solution inefficient in terms of complexity,
reliability, data integrity, time required to debug, etc.

Bill K.

Site Timeline