In any database application you need drop downs, but lot of times most of your drop downs are simply Id and Type. Example For Gender (Male,Female) you have table called Gender with GenderId and GenderName For Salutation(Sir,Mr,Mrs) you have table called Salutation with SalutationId and SalutationName

Therefore in Employee table, you will have GenderId and SalutationId as FK.

I have also seen one generic table that has Category,Type and Id to handle the same information.

So for the above example the table would look something like this

Category ChoiceCode Id Gender Male, 1 Gender Female, 2 Salutation Mr, 3 Salutation Mrs, 4

The second approach (One generic table) seems easy and does not require creating new table every time we have Type and Id drop down.once you add it to LLBLGen project and once you write one function that takes category as parameter that gets you the desired values, you are set.

But the drawback is there is no real FK or constraints. Because all drop down values remain in this table, it gets used by all scope of the application and could lead to deadlocks?

The first approach(separate table for each drop down) seems standard, you have clean FK constraints, but requires you to add a new table, add it to LLBLGen project, write function to get the data every time.

I just wanted to get some opinion about which of these two approach is better or if there is any other better approach.

Thanks

IMHO, every approach has drawbacks, is unavoidable, what's important is to define the path you want to follow based upon the real application goal and its future.

The table you showed above works but it can be normalized a little bit more, you might not have referential integrity per concept, just per abstract category. The normal approach is to create more tables, which is normal if you design databases, and if it's well designed maybe you won't touch the DB that much at the point that adding things could be a maintenance problem. You even could just not use any category/type/status/gender table at all, and just set the values at code. At the end of the day, you manipulate your DB though your application code. This could seems extreme, but in fact there are situations where this makes sense. "Convention over Configuration".

HTH

Thanks

Agreed the example table needs to be normalized.

As for just having values in code, i am not sure that works for us. We want all the data source coming from one place from db. We did not want people coding values in SSRS, anroid, IOS...web applications , + having it on db allows us to change values and not worry on its impact too much.

Frans, did you have any opinion on this?

You could also use enums. The lookup tables then aren't used.

So you use

public enum Gender
{
    Unknown,
    Male,
    Female,
    Other,
}

You can add attributes for descriptions to the values (and use them in client code) so you get better descriptions/names)

and in your entity (load the enum using the usual methods in the designer, e.g. with a typeimport file) you define a field of the type of the enum. In the table you then get an int field. As the lookups are only used client side anyway this is sufficient.

It changes if you want to define extra info with the lookup value. It also changes if the values for id used (here they're defined in the enum, they're the enum numeric value) are used elsewhere in relationships for example or in check constraints or other things: then it is a different case as you then have defined what '1' is in two places.

Point taken. Thank you