Data structured in the database...

[Lashiec]

I've got the lovely task to create a shopping cart for our company to sell (actually, they've managed to sell two of them without it even being created...)

 

Anyways, I put a mock up together a few months ago and I'm really thinking that some of the structure I used for the data shouldn't be the way it is.

 

The companies have large whole goods, and with all of these whole goods comes a bunch of different parts that make up this whole good. So if they pull up whole good A they will get a little picture of it along with a listing of every part in that whole good, as well as current inventory, price, etc.

 

The way that I had set these up was to serialize an array in the database with the part numbers, suggested qty, and reference number. This would have to be stored in some sort of text/blog field so that it had enough room to store all of this information. Some of the whole goods have up to 50 parts in them. But, now I'm really thinking that I should be breaking all of these apart into different rows in the database and just link them back to that whole good part number.

 

So... I use to have:

 

[1] =>

    [qty] 2

    [ref] 4a

    [num] 3043520

[2] =>

    [qty] 4

    [ref] 4b

    [num] 54620

 

and so on serialized into one row in a database that would be tied into that whole good.This could get quite long with 50 parts in that whole good. Not to mention that I also have to have some sort of tracking back if for some reason a part number got deleted from the main inventory database to know that it would be in the above array, so once something was deleted it would unserialize the array, check for the part number, remove it and so on... So a lot of extra work when I'm feeling something like the below could work much better:

 

id    ref    sug    num

1    4a      2      43534636

2    4b      4      45435435

 

and so on. There will more than likely be more things that I'll have to add on here once I get going more into this project.

 

There are a few other things that I serialized and stored (shopping cart, previous orders) in the database too that I'm thinking should be broken out into their own separate rows. Not to mention when I pull the above out I need to grab the list of part numbers and queue the main inventory list for the price, number in stock, and so on, so I can't really do much of a join function on that which could save some time and PHP processing.

 

So, after rambling on, does anyone have any suggestions or comments on how I should go about doing this? Is serializing data something that shouldn't really be done in this case, or at all?

[fenway]

TLDR... care to summarize?