newbie question : Database structure

Hi,

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
Products

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.

Thanks

Chris

Any sugegstions

Re: newbie question : Database structure

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

Cheers
Chris

"no.one" wrote in message
news:c_2dnXoeYcyhBK_ZRVnygQ@bt.com...
> Hi,
>
> 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
> Products
>
> 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.
>
> Thanks
>
> Chris
>
> Any sugegstions
>
>
>
>

Re: newbie question : Database structure

no.one wrote:
> 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?

That's exactly how this is best done.

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

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.

Regards,
Bill K.

Re: newbie question : Database structure

"Bill Karwin" wrote in message
news:e0u8ab013ra@enews4.newsguy.com...
> no.one wrote:
>> 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?
>
> That's exactly how this is best done.
>
>> Alternatively I wan thinking about a associative array as a field in the
>> Order table that would contain Product=>Qty pairs.
>
> 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.
>
> Regards,
> Bill K.


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
possible.

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.

Cheers
Chris

Re: newbie question : Database structure

Chris wrote:
> 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.

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.

Regards,
Bill K.