All dropdownlists in 1 table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

All dropdownlists in 1 table

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-07-03 : 08:56:02

First: I *Love* this site

I've developed some applications (webbased) which works fine. Usually I end up with more than 200 tables. Now, I'm starting from scratch with something new, and I want to do some things different but without falling into new pitfalls en reinventing the wheel.

(1)
Is there a major drawback on putting all dropdown listst into one table. So... Colors, Type of Media, Material, Urgency level etc. are typically things I used to put in different tables like tblUrgency, tblMaterial, tblColor and so on.

Now I have 2 tables instead of all this tables:
tblChoice and tblChoiceList where 1 Choicelist record can have several 'Choices'. I even add some extra colomn names in tblChoice as sequel, Deleteable, Active and so on.

It's easy to maintain all these lists, and easy for dynamic programming, escpecialy when some client wants an extra Choicelist.

But is this common practice? Can anyone shine a light on this?

(2)
I have normalised table-colomns. Every first colomn is always the unique key and is named Id which is an int and increments with 1 on every new record.
If it's something which has a name (like tblTree --> Poplar, Oak, etc.), then second Colomn name is Name.

Again, I think it's easy to dynamicaly make some code this way.

But is there a recommended way to name tables columns, primary keys and such?

Thanx in Advance!!

Henri

~~~
SQL is nothing, writing it everything.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-03 : 10:52:12

I've seen systems employee a table that stores all of the code tables in to one, of course you'll lose then benefit of RI in doing this but:


CREATE TABLE ENR_XLATTABLE (
       FIELDNAME            VARCHAR2(18) NOT NULL,
       FIELDVALUE           VARCHAR2(4) NOT NULL,
       EFF_STATUS           CHAR(1) NULL,
       XLATLONGNAME         VARCHAR2(30) NULL,
       XLATSHORTNAME        VARCHAR2(10) NULL
)

Brett

8-)

dsdeming

479 Posts

Posted - 2003-07-03 : 13:12:43

quote:
But is there a recommended way to name tables columns, primary keys and such?

Everyone has his own preference, and in the long run consistency is probably as important as anything else. My 2 cents worth: Never use spaces in object names, unless you enjoy the carpal tunnel syndrome that comes from pounding the square bracket keys all day long; and never, ever use SQL Server reserved words as object names, unless the idea of early ( and probably involuntary ) retirement is appealing.

Otherwise, don't be shy about typing. Descriptive names are the best, even if they tend to be longer.

Dennis

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-03 : 15:06:31

I personally think EVERY list of lookup values should in it's own specific table, with as many columns as you can add that help identify what makes each possible value for that lookup different from the others.

For simple attributes that really have no meaning (i.e., like "Color" a lot of times) then you could put them all in 1 big generic table.

But for any lookups that have any meaning, put them all in their own table, and add as many columns to those lookup tables as you can.

For example: suppose you have a table of "permission levels":

Read-Only
Normal
Supervisor
Administrator

Instead of just listing them out, and in all of your queries and in your code saying things like

if Perm_Level in ('Supervisor','Administrator') then [allow editing]

I prefer to add as many columns to the lookup table so that you can say:

if CanEdit = 1 Then [allow editing]

or

if CanView = 1 Then [show ....]

or

if CanPost = 1 then [allow posting ...]

..etc...

and each of those "Can..." 's would be a column in your table of possible permission levels. This means that as permission levels come and go, or as they are renamed, you never need to edit your code. The only time you would edit your code is add you add functionality to your application, which of course makes sense!

And that also means that maybe someday there is a permission level you didn't anticipate when you first designed the app (e.g., maybe they can post data, but not edit it) -- you just add a row to your table, set the appropriate values for the permissions, and you are done! No editing of code or changing your apps in any way.

Just my $.02 on all this ... but you'll find that if you really put intelligence into all of your lookup tables, your applications practically write themselves, and maintaining them is very easy, and they also are very readable .

And, when you open up one of those lookup tables to see the possible values, you get an EXACT picture of the difference between those values, what they mean to your application, etc. If you want to know "what is the difference between Supervisor and Adminsitrator" there is no need to go through every line of code in your app -- it is all listed right there in the "Permission Levels" table !!

- Jeff

Edited by - jsmith8858 on 07/03/2003 16:47:35

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 09:53:39

I agree with Jeff 100%.

So with that said, why does it seem that EVERY major software company build highly dynamic structures like the one I posted?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources