Basic Database design. Use a another table or colum

Question

I have a table that holds information about a particular Object, Say Item and has columns

ItemID, ItemName, price, ItemListingType.....LastOrderDate

One of the bits of information, ItemListingType could be one of 10 different types such as:

private, gov, non-gov, business... etc (strings) and could be extended to more types in future.

Should I be using a column inside table ITEM or should I Use a separate table with two columns and put a foreign key in Item table to reference that (a one to many relationship)? Like:

• ListingTypeID int

• ListingTypeName varchar(MAX)

EDIT: how many values for a column, you will consider to use another table for that

2, 4 or what ?

Thanks

Answer 1

I would definitely go for a "lookup" style column; that way you are not stumped when there future additions to the list of permissible listing types. You are also reducing redundancy and making it easier to change the designation of aparticular type of listing (if "gov" changes to "government agencies", then you only have to change it in one place).

Answer 2

Use a separate table to store this kind of reference data. This is a tenet of normalization and will also enable easier caching because you are separating read-only and read-write data. my two cents...

Answer 3

Clasically, you should use an extra table, because you won't have any duplication that way. It will also allow you to change the value for this listing in a single place. However, if you are very very sure that no types will be added, keep the column.

Answer 4

You should do it with the second table that holds the ListingTypes and link to the id of that table from the one with the Objects...

Take a look at Relational Database and Relational model.

Answer 5

Separate table.

• What if you have a listing type not yet used?
• Or delete the last item with type x?
• Or need to change a value?

These are insert, update and delete anomalies, which is one reason for normalisation

Answer 6

In situations like this I ask myself: Can the Item have an undetermined number of Listing types? If yes, different table.

Do the specs say that there will never be more than 3 types? Depends. Sometimes I'll still go with a separate table, sometimes not. You get a feel for this after a while.

Will the Item ALWAYS have a single listing type? If yes, same table, single column.

---

Now to take matters one step further. If an Item has zero or more listing types AND those listing types are actually shared (in other words two items could have the same listing type, then we have 3 tables: Items, ListingTypes, and a cross reference table to support a many to many relationship.