One Table Two Tables 1

[wolfdba]

Hi All,

A developer asked me to post this question:

When developing software for new requirements there
are two possibilities how to set up the database. Either to
add flags to an existing table, or to create a stand-alone
table for the software.

Example There is a need in our company to be able
to identify inventory items that will be free of shipping
charge. Our software needs to supply the company a way
how to maintain those items and our order entry should
recognize these items and not charge for shipping.

The question is should we maintain those items in a
separate table besides being in the main inventory table and join to the
main inventory table by a primary key or
do we just add a flag in the main inventory table
to identify these free shipping items.

I appreciate your response,

Zev Berezin
B&H Photo
Email: zevb@bhphoto.com

 

[olded]

Zev:

As a DBA and developer, I've had this fight before. If you add a flag to your main inventory table, you'll be adding another flag the next time another requirement arises.

Not only would I have a separate table - call it a inventory reference table - for storing items not requiring shipping, but I'd add some flexiblity for future requirements.

Consider this tiny un-real world example:

Given the main inventory table:

create table inventory (
keyitem serial, # primary key
description char(whatever),
.
.
}

Here's a possible reference table

inventory_reference (
keyitem integer, # foreign key, duplicate
key serial, # primary key
reference_name char(15), # name is "noship"
reference_value char(15), # value is "YES"
}

I realize the keys probably do not agree with your database, but you get the idea. Now, when an inventory is flagged for no shipping, create an entry in the inventory_reference table for the item, set the reference_name to something like "noship" and the value to possibly "YES".

This provides more possible values to "noship" other than a boolean flag.

And what about the next requirement that nobody has dreamed up, yet? Imagine having to send a free sample to a customer - call it "freesample". Create an entry in inventory_reference for the item, set the reference_name to "freesample", etc. You get the idea.

Also, if your environment is like mine, the master tables are very important. With a reference tables design, you tend to keep your hands off the core, primary tables.

Of course, this is just my opinion.

Regards,

Ed