Lookup Table

Question

Post reply

Lookup Table

bpowers

SSCertifiable

Points: 5483
More actions
September 6, 2011 at 3:55 pm

#390353

I am building a small database for one of our developers. Their application will include several drop down boxes/combo boxes. Should I create a lookup table to populate the data in the drop downs or let the developer add them to the front-end application. What is the best practice approach to this? I am sure at some point new items will need to be added to the drop downs. Any help or guidance will be greatly appreciated.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I prefer to have those items pulled from a lookup table in the database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

NotoriousDBA SSChasing Mays Points: 658 More actions · Answer 2

I second that. Also, I'd recommend adding a flag to all of your lookup tables to allow entries to be marked as obsolete.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I always use lookup tables.

I would not just let anyone add values to the table. THis need to be well thought out or you will have issues.

I add an Active Boolean (Bit) Column to control which values are displayed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Welsh Corgi (9/6/2011)
I always use lookup tables.
I would not just let anyone add values to the table. THis need to be well thought out or you will have issues.
I add an Active Boolean (Bit) Column to control which values are displayed.

That is what I commonly do as well. That way the list can be somewhat controlled in case changes need to be made in the future.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

Welsh Corgi (9/6/2011)
I always use lookup tables.
I would not just let anyone add values to the table. THis need to be well thought out or you will have issues.
I add an Active Boolean (Bit) Column to control which values are displayed.

I agree with using a lookup table, and I agree with restricting access to the allowed values, but make sure removal as well as addition is protected.

Also, don't forget to include a label column alongside the value column so that the developer can display the label to the user, but select the value.

for example

label = "Next Day Delivery Before 10am"

value = "delivery10am"

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

mister.magoo (9/6/2011)
Welsh Corgi (9/6/2011)
I always use lookup tables.
I would not just let anyone add values to the table. THis need to be well thought out or you will have issues.
I add an Active Boolean (Bit) Column to control which values are displayed.
I agree with using a lookup table, and I agree with restricting access to the allowed values, but make sure removal as well as addition is protected.
Also, don't forget to include a label column alongside the value column so that the developer can display the label to the user, but select the value.
for example
label = "Next Day Delivery Before 10am"
value = "delivery10am"

Very good points.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Make sure that you add a Foreign Key Constraint.

You will not be able to remove Codes for that would be a violation of the Foreign Key Constraint.

Setting the Active/Inactive Column to Inactive has the same effect as deleting the record while maintaining referential integrity. This should be controlled.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

I was going to talk about foreign key constraints, but it came out wrong...

I will try again.

I would urge you to be careful when using an active/inactive flag as well, as the developer may include referential constraints in his code model.

This is fine as long as you are adding records through the application, but when you come to edit a record which contains a value that you have now marked as inactive, the constraint will not be violated in the database, but could be in the application - as the application will likely be retrieving a filtered "active" list for the lookup values and the current value would now not be valid in the lookup collection.

Hope that made sense :hehe:

(I deliberately used the word "record" as I am talking about the application contraints, not the database constraints and each record may be stored across many tables)

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

The way that I have handled Active/Inactive Flag is that the SP that is used to populate the List Box with a WHERE Clause that filters the records.

Respectfully there is no need for the developer to incorporate referential integrity at the application level.

Changing the values is controlled by the production DBA.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 10

Welsh Corgi (9/6/2011)
The way that I have handled Active/Inactive Flag is that the SP that is used to populate the List Box with a WHERE Clause that filters the records.
Respectfully there is no need for the developer to incorporate referential integrity at the application level.
Changing the values is controlled by the production DBA.:-)

Fair enough, I expect we have different needs.

I do use referential integrity at the application level as it prevents unnecessary attempts to insert data into the database that will certainly fail.

I accept that you do not see a need for it, but I feel it is worth mentioning in this thread in case the OP's developers work that way too.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

mister.magoo (9/6/2011)
I do use referential integrity at the application level as it prevents unnecessary attempts to insert data into the database that will certainly fail.

What you are referring to is form validation.

If you have a Foreign Key Constraint and you populate the List Box from the Lookup Table then the DML Operation will not fail.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

bpowers SSCertifiable Points: 5483 More actions · Answer 12

Thanks for all the feedback. It is greatly appreciated.

vikingDBA SSCertifiable Points: 5356 More actions · Answer 13

Another great advantage of using a drop down list, as we have learned, is that it gives greater opportunity to do reporting. It allows you to easilly determine how many records have drop-down id 1, how many have drop-down id 2, etc. Reporting is very near impossible if you use something like free-form text to do the values. Even if you look for a specific string, there is no guarantee the values are spelled correctly in the data. Also allows selectivity on select statements, if you just want to see records with drop-down id = 3.

I also use a CurrRec column (bit) to denote whether the record is a current record. Allows the users to turn off unwanted drop-down values (like if inspectors leave the company, etc.), so they don't appear in the drop-downs for any new records. However, the data is still there for historical records (reporting).

I go an extra step and also include a SortOrder (int) column, and default it to 999. That way, the select statement that builds the list of possible drop down values not only pulls just the ones with CurrRec=1 (true), but also does an ORDER BY on the SortOrder field first, then by either the primary key or the lookup value column (to put in alphabetical order). This allows the users to move values to the top of the drop down list that are used often, by resetting the SortOrder value to something less than 999, even if those values are far down in the lookup table (in relation to the PK).

Just a suggestion. It has come in handy.

We also employ triggers to set the Last Change Host, User, and Date. (The last user to modify the data, the date, and the workstation name that they did it from.)

Sample table:

CREATE TABLE LUPosition

(PositionID int IDENTITYNOT NULL

CONSTRAINT PK_LUPosition_On_PositionID PRIMARY KEY CLUSTERED,

PositionNamevarchar(20)NOT NULL

CONSTRAINT UC_LUPosition_On_PositionName UNIQUE (PositionName),

PositionDescvarchar(200)NULL,

CurrRecbitNOT NULLDEFAULT (1),

SortOrderintNOT NULLDEFAULT 999,

LCHostvarchar(50)NOT NULLDEFAULT HOST_NAME(),

LCUservarchar(50)NOT NULLDEFAULT USER,

LCDatedatetimeNOT NULLDEFAULT GETDATE()

)

GO

Nadrek SSC-Insane Points: 20039 More actions · Answer 14

If you're going to use the values in joins, use referential integrity, or otherwise have value added by a relational database, yes.

If they're going to pull the data to their app and never use it in any other way in the database, no; have them create text files, use web.config/global.asa, ini files.

i.e. "Does the database add value"? If it's just a place to store things, used because "well, we've got other stuff in there", then no; don't put your pencils in the fridge; it doesn't add value.