Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Crossposted to Stack Overflow: Database design for part-number generator

I'm working on a generator for part numbers, where each space will have a meaning and each space will have a number of possible values. What is a "correct" database design for this?

Example part number layout: A B C D - ####(0000-9999)

A = Fabric backer (set length of 1 digit, 5 different options)

B = Stitching type (set length of 1 digit, 10 different options)

C = Stitching color (set length of 1 digit, 3 different options)

D = Trim color (set length of 1 digit, 10 different options)

#### = can be any 4-digit long number (allows preceding or following zeroes) that corresponds to a fabric color that we use (not all numbers between 0000-9999 are valid, but it isn't in serial order, either, as there are gaps)

We will be creating new part number generators in the future, and they will have different layout, different possible values, and different lengths. I was thinking of doing something like this:

TABLE: GENERATOR (the overall instance of generator)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int (autoincrement), 5
name, str, 100
is_active, bool, 1
notes, text, 999

TABLE: GENERATOR_VALUESET (connects the generator with the spaces for valuesets, basically sets the space order and valueset used for each space)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
gen_id, int, 5 (correlates to GENERATOR.id)
valueset_id, int, 5 (correlates to VALUESET.id)
ordinal, int, 3
is_active, bool, 1

TABLE: VALUESET (collection of associated values to form a coherent valueset relevant to a particular product option)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int(auto increment), 5 
name, str, 100
is_active, bool, 1

TABLE: VALUESET_VALUE (connects values to the valueset and sets order)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
valueset_id, int, 5 (correlates to VALUESET.id)
value_id, int, 10 (correlates to VALUE.id)
ordinal, int, 3
is_active, bool, 1

TABLE: VALUE (the actual values, includes a displayed value as well as a long-format name)

FIELD_NAME, FIELD_TYPE, FIELD_LENGTH
id, int(auto increment), 10 
name, str, 100
displayed_value, str, 5
is_active, bool, 1
share|improve this question
    
Does this still need a link to the so-called "cross posted" version, if the cross posted version is closed? Seems like it would be confusing for others who may find this question later. –  m.ed Sep 21 '12 at 21:19

2 Answers 2

up vote 4 down vote accepted

First, I don't see any problem with doing this in the db. In fact we are moving this direction with LedgerSMB. The big caution is that you want to be prepared for business rule changes. Of course your old partnumbers will be stable. but SKU's are stable for a long time usually unless you are Frito Lay and then it just makes trouble for everyone else (they change UPC's it seems every time a word on their packaging changes).

What we've done in LedgerSMB is to essentially store a template, and then we preprocess and increment. That isn't quite what you are looking at doing, but it may help to see how we have looked at this in the past and where we are going.

Our templates look something like:

<?lsmb field_name ?>somechars<?lsmb another_field?>0001102

Leading zeros are preserved on increment. Inside the db, we have a stored procedure which increments the number, keeping leading zeros and passes the rest up to the application. I don't see how we can do full preprocessing of this sort using invoice numbers and the like but for partnumbers the goal eventually is to move all that preprocessing into the database.

Now in your case, you probably want to have the parts tied together for easier management. I understand the arguments against this. I would suggest putting all these fields you want to aggregate in the table representing the parts. Then you can integrate these with a user defined function or something (that's how I'd do it in PostgreSQL) which you can use to populate a missing part number.

In this case your table structure will look very different:

product_group(id-- 0000-9999, etc)
fabric (id ....)
etc....
part (partnumber, productgroup_id, fabric_id, etc)

I don't see another sane way of doing it. You are going to have to track each of these things you want to use separately, put them in the parts table, and then generate your partnumber based on that.

share|improve this answer
    
This helped a lot, thank you. –  m.ed Sep 21 '12 at 21:35

Your desire to make a generic solution for all part number generators comes at the expense of making a good one for the domain you are in. VALUES and VALUESET tables could be used to represent almost anything. The components of your part numbers (Fabric Backer, Stitching Type, Stitching Color, etc.) are good candidates for well-defined database tables and the resulting part numbers are also good candidates for being stored in the database.

The part number generation itself, however, should probably be in code. This code can (should) be in the database and may come in the form of one or more functions depending on how many different types of part numbers need to be generated.

Some of the difficulty in answering your question is that we don’t know the scope of your part numbers. If your part numbers will pull from mostly the same sets of data, then you might even consider referencing the source tables in the table with the generated part numbers. Not only would this enforce valid data, it would also allow joining to see things such as part numbers and their Fabric Color descriptions. The part number itself could then even be a computed column in the table itself.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.