Getting away from a lookup table, how?

Question

Getting away from a lookup table, how?

josh.d.kelly

Newbie

Points: 3
More actions
August 13, 2009 at 9:59 pm

#385181

I am a web developer trying to become a better database designer. I have been reading about keeping away from lookup tables. I need some help to figure out to do that considering my current DB has alot of them.
I have two tables a user table and a group table. All groups must have at least one user, but a user does not have to belong to a group. These are sample tables and both tables have more columns.
Users
--------
uID(INT) Primary Key
userName(VARCHAR(50))
Group
---------
gID(INT) Primary Key
groupName(VARCHAR(50))
UserGroupLookup
--------------------
uID(INT)
gID(INT)
I need to be able to scale to have thousands of groups and a million + users. I have the lookup table to allow for quick sorting so when gID = 2 is viewed then all the users can be quickly found and user data joined.
Thanks for your help!
-Josh

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

josh.d.kelly (8/13/2009)
I am a web developer trying to become a better database designer. I have been reading about keeping away from lookup tables.

Do you have a URL where you've been reading that misinformation? I'd like to get kick the slats out of someone's fence for giving that bad advice. The tables you've show are pretty much properly normalized. Any changes would make your tables much less proper and much more difficult to manage in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 2

josh.d.kelly (8/13/2009)
I am a web developer trying to become a better database designer. I have been reading about keeping away from lookup tables. I need some help to figure out to do that considering my current DB has alot of them.

I suspect this is just a terminology problem. The term "lookup table" rarely seems to be precisely defined and certainly means different things to different people. Most often it is used to mean a table that is referenced by a foreign key. For the record, there is nothing inherently wrong with having tables referenced by a foreign key!

In the Microsoft Access world "lookup table" can imply something else: lookup fields. The use of that feature in Access is indeed widely discouraged and that may be what you have been reading about.

My advice is to base your database on good design principles like normalization and try to avoid vague terms like "lookup table".

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

To summarize what I think that we all agree on: What you have is fine and there is no apparent reason to change it.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Steve Jones - SSC Editor SSC Guru Points: 738693 More actions · Answer 4

I'd agree with Mr. Moden and Mr Young on using lookup tables. And I'd encourage one of them to write something up on this.

Josh, If you have a URL, we'd like to see it and try to write something against it.

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 5

Add another vote of confidence supporting you for doing well on your initial design. Now don't mess with it. There's nothing wrong with using lookup tables. As a matter of fact, everything is right about using lookup tables. Stick with them. You get better data integrity and less programming headaches.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

josh.d.kelly Newbie Points: 3 More actions · Answer 6

Thank you for your replies. I was getting confused about the terminology and couldn't find the answer. So thank you very much. I will continue to create tables like this. As soon as I have the site live I will reply back so you can see the pieces come together and then let me know how I did.

Thanks!

Josh

josh.d.kelly Newbie Points: 3 More actions · Answer 7

In this table

UserGroupLookup

--------------------

uID(INT)

gID(INT)

Do the columns need to be foreign keys?

Thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

They don't NEED to, but they probably should be. That will help keep away from "orphans".

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 9

I agree with Matt. Need is a big word. I'd say they should be and are probably also the primary key & clustered.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning