Re: Database design suggestion needed
Posted by: Rick James
Date: February 16, 2014 01:53PM

Executive summary: More application work; less SQL work.

Details:

* An Item is either in a Set or in a Magazine. (Or possibly gone == DELETE it.)
* An Item can have a quantity, rather than listing individual instances of identical Items.
* An Item may have attributes that may need to be matched exactly, or only approximately (as in the resistor example).
* On the presumption that attribute ranges can overlap, it would be impossible to prematurely group Items. (again the resistor example)

Based on those thoughts, I would go with a hybrid approach.
* The Database contains the "source of truth" about what there is at the current moment in time.
* Some application code (PHP?) would do the messy work such as fuzzy resistor matching.

The database:
* Items = Inventory of parts:
-- PK (id)
-- Index type (resistor, etc) + location (which Magazine or Set)
-- other fields: distinguishing characteristic(s) (eg, resistance value), cost, etc. Note: `quantity` is not here.
* Set -- mostly just an Entity for an Item to belong to.
* Magazine -- mostly just an Entity for an Item to belong to. Hmmm; smells like a Set, at least from the database point of view.
* What's where - a Relationship table (and a place to hold `quantity`):
-- PK(Item_id, Mag/Set id)
-- quantity

Then...

When you purchase a new resistor, use application code to discover whether there is already an ID for that resistance value. if so, bump the quantity. If not, create a new record.

The application would be given a list of desired Items, and go searching through one Magazine for Items with the desired characteristics, doing what ever is needed to say 47K will be ok for a 10K-100K requirement. It would check quantity; it would have messy logic to say the 3 resistors could be both of the 47K in the Magazine, plus 1 of another resistance.

In the process of doing the search, it could build a 'tentative' Set and gather a list of missing parts. After you purchase the missing purchase the missing parts and put them into Inventory (Items), you might want to undo the Set and re-search for which parts to put into the Set.

Bottom line: You probably should not try to do too much in SQL; leave the messy stuff for a real programming language and/or human interaction.

An example of human interaction: you could display all the available resistors ("available" = in Magazine#1), together with quantity and characteristics, plus UI boxes to fill in for how many to take from the 'inventory' for the Set you are building.

Another action might be to migrate certain Items from Magazine#1 to Magazine#2. This would be similar to converting the items from being in a Magazine to being in a Set.

A Set must be in a Magazine; a Magazine must not be in another magazine. This feels like a NULLable field that is a Set/Mag id. Sets have it non-NULL; Magazines have it NULL. IS NULL would let you distinguish which are Sets; which are Magazines. (If a Set can be part of another Set, then another flag would be needed.)

Options: ReplyQuote


Subject
Written By
Posted
Re: Database design suggestion needed
February 16, 2014 01:53PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.