473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How should you represent discrete states?

Banfa
9,065 Expert Mod 8TB
Right I am actually using SQL Server 2005, however this is a general question about table design that is probably relevant to any database engine so I am going to ask it here since this is the busiest DB forum on the site.

To start with I make no bones about the fact that the area I am really expert in is C/C++ programming on a variety of micro controls and computers.

And the problem is this, what is the best (or correct) way to store a number of discrete states in a database table, for instance

DOWNLOAD_READY
DOWNLOAD_FLAGGED_TO_START
DOWNLOAD_IN_PROGRESS
DOWNLOAD_COMPLETE

The thing is that programmatically speaking use of strings is something that we (C/C++ programmers) avoid where possible, they are just not efficient so we like to use numbers. However we also like to avoid magic numbers, that is numbers in the code that appear without there meaning being clear, so in this case I would use a enum

enum download_state {
DOWNLOAD_READY
DOWNLOAD_FLAGGED_TO_START
DOWNLOAD_IN_PROGRESS
DOWNLOAD_COMPLETE
};

In C/C++ this is a way of associating the text that the programmer sees with a number, the compiler automatically assigns numbers (starting at 0) to each item in the enum so the programmer can use the symbols defined to make the code make sense and avoid magic numbers but once compiled the computer is actually using numbers which are efficient for it.

Back to database tables, suppose I wanted to store the same information in a column of a table (and I will want to get able to sort on it or at least pick out entries at the DOWNLOAD_READY state)?

Should that column be text but this seems to have the issue of probably being less efficient and also not really limiting the entries to valid values.

Or should the column be a number, I can probably constrain the number into the right range if I wish, however the table then contains a column of numbers whose meaning is not immediately obvious, i.e. it contains magic numbers.

I am aware that some SQLs actually contain an enum type solving the problem in more or less the same way as the C/C++ programmer would but I believe that is not a standard SQL type.

So there is my question, what is the best/correct standard way to store this type of data in an SQL database?
May 9 '08 #1
2 1418
Stewart Ross
2,545 Expert Mod 2GB
Hi Banfa. There are no absolute answers to your question, as what should and should not be in any particular table is a matter of who does the design more than anything else. Ask two people and get three views...

I would store the states in a small table with an integer primary key (for efficiency of access later) and a string state name. The users would not see the key - it is just a means of identifying the rows internally.

Although it can be argued that it is meaningless to introduce a separate integer primary key when the states themselves are candidate keys this ignores the very real efficiency problem of string indexing/lookups vs integer indexing. Like you, I would consider it highly inefficient to be accessing the string values as keys instead of much simpler integers.

(As an aside, enumerated types in VB are also defined using integer values underneath just as you describe for C++.)

There are occasions when I would add one more field to such a reference table - an integer sort order. I do this when I wish to provide users with a specific order for the list of values (in drop-down lists and so on) in a way that makes most sense to the users, should this be required. A query on the base table sorted on the manually-defined sort order field provides a view of the data which is independent of the integer primary key and of the alphanumeric ordering of the strings.

In my view it always makes sense to record reference data in a small table using an efficient primary key indexing method, even if this means introducing a numeric key value that is not that altogether meaningful to humans but is very efficient for machine storage (and, more importantly, for fast data retrieval).

I am sure there will be other views here, but it's the method I use all the time.

-Stewart
May 9 '08 #2
Scott Price
1,384 Expert 1GB
Correct me if I´m wrong, but looking at what you have I assume you have one subject; Downloads, with various characteristics: Ready, Flagged_To_Start, etc.

That would mean one table called Downloads, with a primary key column and four boolean columns.

Each Download instance gets a separate record that records the boolean values of each characteristic (or Date-Time if you are intending this to be a logging table).

As Stewart pointed out, two people will give three opinions :)

Good luck, Ben...

Regards,
Scott
May 9 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Sim Nanda | last post by:
Hello All, I'm looking for a data class that can take a floating point input and round it to the nearest float based on a predefined set of boundaries. In other words, an arbitrary float comes in...
2
by: Steve | last post by:
I don't get it. In Codewarrior for Mac OS 9.4, numeric_limits<unsigned char>::digits10 == 2. Unless I don't understand it properly (most likely!) I thought digits10 was supposed to represent...
2
by: Mike | last post by:
I'm more unsure of myself these days: Given a result set of rows with discrete date intevals; but rows may be non contiguous in nature; Can a simple, non cursor driven query produce a result with...
2
by: Francois Vanderseypen | last post by:
Imagine a discrete 2D space of any size. Say, you have a 100x100 pixels (.net) panel and you have discrete cells of 10x10 pixels. Thinking e.g. of the game of life. I would like to draw a line in...
10
by: jack | last post by:
Hi guys, I am working on a project which requires an implementation of discrete event simulation in C using linked lists. I would greatly appreciate if someone could provide with some sources...
0
by: Alison Givens | last post by:
I have the following problem. (VB.NET 2003 with CR) I have a report with a multiple-value discrete value and a rangevalue. The report shows fine in the viewer, but when I hit the export to pdf...
1
by: dan_williams | last post by:
Is it possible to pass multiple discrete values to a report document so that I can export it to PDF? I've managed to perform the following code to display a Crystal Report Viewer ok, but i want...
0
by: lrobo01 | last post by:
I'm having a problem with Crystal report.net with ASP.NET. The problem occurs when exporting the report. The report uses a discrete parameter with multiple values. When the report is loaded into...
0
by: lrobo01 | last post by:
I'm having a problem with Crystal report.net with ASP.NET. The problem occurs when exporting the report. The report uses a discrete parameter with multiple values. When the report is loaded into...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.