CODEWITHSUNDEEP
database-design
DMA57361
DMA57361

Reputation: 3690

Should defined numbers be stored in a table within the database?

Imagine you have a table - lets contrive an example where this is Orders - which has a field named type, which may be only one of a few fixed values (lets say 0 or 1, for Quotation and Order).

Should all the possible values of type be represented by another table in the database (with just typeID and typeName columns?), and Orders made to reference the new table?
Or is it normal to leave these numbers as-is and have them documented outside the database?

If this is a "it depends" situation: What are the important implications of both options?

I have used the first option in a recent University assignement, but without really thinking about why - and looking back at the design now I'm wondering whether this is important.
I never once queried the reference table as part of my application - because I didn't "need" to - does this indicate the reference table is unneeded, or is this a flaw in my application because it should be checking the ref table (what for?)?

Upvotes: 0

Views: 99

Answers (5)

Tomas
Tomas

Reputation: 553

I think the biggest difference is that when you include the reference tables with the "lookup" data in the database (even tho they will never be used by the application), it can serve well later to other developers when they work on the system. The advantage over the "document it in the documentations somewhere" is that keeping it in database forces you to update the reference tables when new values are used in the application. Written documentation tends to get out of sync over time.

Another point is that keeping the reference data in database might help in situations when some other team needs to hook up to your application on the database level (i.e. for some data mining, reporting etc).

Upvotes: 3

Michael Sagalovich
Michael Sagalovich

Reputation: 2549

My five cents are for lookup tables, as (except for what others say) you can automatically generate enums over these tables in the code, thus keeping your front-end code in sync with database.

Upvotes: 0

Matt
Matt

Reputation: 3848

If its a value that only has meaning for that table, then it suggests an enum type for that field.

If the value has meaning in multiple tables, I would use a reference table like you did.

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166406

Typically a second table with typeID and typeName, with typeID as primary key, and a foreign key in the Orders table.

These can be used later for filtering from the client side.

And also, later there might be more information pertinent to the actual type, that can then be stored in the same table.

Upvotes: 1

Freddie
Freddie

Reputation: 1707

use enum: http://dev.mysql.com/doc/refman/5.0/en/enum.html

Upvotes: 0

Related Questions