A DB design problem
I have a table, OrderDetails, which represents the detail rows of a master Orders table.
Details of an order can reference both stock items (which are saved in an Items table, with fields: ID, Code, Description, UM) and uncoded "free" items, i.e. items whose Code, Description and UM are input by the user "on the fly".
I have structured OrderDetails this way:
- ItemType (1 for stock items, 2 for uncoded items)
- ItemID (foreign key relative to an ID in the Items table,
which contains a special record with ID=1 for uncoded items)
- itemCode (30 chars)
- itemDescription (200 chars)
- itemUM (foreign key relative to an ID in the UM table,
which contains a set of default UMs)
- ID, qty, price, etc (irrelevant for the discussion)
While for coded items itemCode, itemDescription and itemUM are just a copy of the equivalent fields in the Items table, as said above I want that for uncoded items the user is able to input code, description and UM freely, according to his/her needs. An uncoded item is therefore identified across the system not by an ID, but by its own instantiation, i.e. by the triple (itemCode, itemDescription, itemUM)
I expect OrderDetails to contain 100.000s or even 1.000.000s of records: when accessed by item, performance in joins and lookups can be become a bottleneck.
There are other tables which can reference items (both coded and uncoded), so joins could involve two or more tables, each containing 10.000s or 100.000s records.
There is at least a problem with the current design, i.e. even for a simple lookup, one needs two different SELECTs depending on the type of item he/she's looking for:
- SELECT ... WHERE itemType=1 AND itemID=XXX
- SELECT ... WHERE itemType=2 AND ItemID=1 AND itemCode='xxx' AND itemDescription='yyy' AND itemUM='zzz'
(TBH, the second SELECT would be enough for both types, if it's guaranteed that item's code, description and UM are kept identical across Items, OrderDetails and all other tables involving items, but I prefer to drop such assumption. So, for coded items ONLY, their identity is their ID, but NOT their code, description, UM)
A related problem of the second SELECT above is that, for performance's sake, indexes on itemCode and itemDescription are strongly suggested, but I don't like indexes on long string fields (itemDescription is 200 chars long).
I could think about various alternative designs, with different compromises between speed, space and code clarity, until I got the "A-ha!" solution I eventually settled on. I'm not describing it here NOW because I'm curious to read your opinions, I'll add it later.
So what would you do?
- throw the current design away, and substitute it with... ?
- change the current design, and if so how?
- keep the current design, and integrate it with... extra fields? extra tables? what?
Just one note: I know that they are trendy, but I'm not interested in noSQL solutions :-)