CODEWITHSUNDEEP
database-design
DNN
DNN

Reputation: 163

Database design; should I use numbers or descriptive text?

I'm working on a simple order HTML form and I'm not sure what the best way to store this in the database would be.

Lets say I have a form where I ask the user:

"How would you like to order to be delivered?"

Using radio buttons they can pick either one. My question is, how do I store this in the database? What should the value be?

<input type="radio" name="delivered" id="delivered" value="">

I could use a descriptive text send, digitally, pickup and save this as a varchar or I can save it as an int — 1, 2, 3.

With integers I remove the possibility of typos and it might even be faster (?). Downside is; I have to remember what each value means. "Was 2 pickup or digital?"

Upvotes: 2

Views: 1375

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52117

A databases should first and foremost care about storing data. How the data is presented to the user should only be of secondary concern.

So in your case, use an integer in combination with CHECK (field IN (...)) constraint (or possibly enum if available in your DBMS), and make sure each possible value is properly documented. This will benefit both integrity and performance (smaller is generally faster) at the database level. On top of that, this can be naturally used by the client code to distinguish between radio buttons, instead of using strings of locally-defined integer/enum values that would then need to be translated to "DB values".

After you have done that, you can consider whether you also need any centrally stored presentation-related data. For example:

  • Do you want the clients to be able to change radio button labels automatically?
  • Do you want to implement a localization mechanism centrally?
  • Etc...

In cases like this, you would typically create an additional "lookup" table listing all the valid integer values together with any additional presentation data necessary. The "main" table would then have a FOREIGN KEY towards the lookup table.

Upvotes: 3

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

A typical design would be to have a separate table, DeliveryTypes say, that contains the mapping of 1 = send, 2 = digital, etc. You would then have a foreign key from the Order table to this table's numeric column. So you get to store just the numbers in the Order table, but you don't have to remember the mapping yourself.

E.g.:

CREATE TABLE DeliveryTypes (
    DeliveryTypeID int not null PRIMARY KEY,
    Description varchar(20) not null
)

INSERT INTO DeliveryTypes (DeliveryTypeID,Description) VALUES
(1,'Send'),
(2,'Digital'),
(3,'Pickup')

CREATE TABLE Orders (
    /* Various columns for Orders */
    DeliveryTypeID int not null,
    constraint FK_Orders_DeliveryTypes FOREIGN KEY (DeliveryTypeID)
        references DeliveryTypes (DeliveryTypeID)
)

If you do go down this route, try to avoid the urge to create the One True Lookup Table

Upvotes: 1

dotnethaggis
dotnethaggis

Reputation: 999

You could consider the following.

You could create an Enum that gives a more descriptive text corresponding to your id.
You could also create a lookup table in your database with the Id (1, 2, 3) and the Text (Physical, Pickup).

If you are going to be doing database queries against the Id it is better to have the lookup table option as you can do a join and bring back the meaningful option rather than just the Id.

Upvotes: 0

Related Questions