Design debate - 1 code table for all codes or 1 for each

Question

Design debate - 1 code table for all codes or 1 for each

chris dietz

SSC Enthusiast

Points: 132
More actions
September 26, 2007 at 9:08 am

#177606

I am in debate with our DBA about storing all codes in one single table or having a code table for each code entity.
A typical system may have 10 to 15 code entities.
Any thoughts?

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I hope this does not start a war again. Short answer is don't put everything in the same table. There's no [real] advantage in doing so and a lot of disadvantages.

Search this forums for lookup tables in the articles sections. When you find an article with over 100 replies, you most likely hit the one I want you to find.

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

Agree with Ninja - separate.

I usually force "code integrity" by using relational integrity (i use foreign key constraints on the fields holding said codes), so I almost always split the codes into separate tables.

----------------------------------------------------------------------------------
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?

chris dietz SSC Enthusiast Points: 132 More actions · Answer 3

Okay, after several hours of reading numerous articles on the debate I agree that a relational database should be design as such.

Thanks for the feedback.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Wow that was quick... the debate I have in mind lasted over a month... You just broke the world record :D.

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

Damn, I didn't even get all the tape wrapped around my fists to start swinging.

Good choice. I'm facing a major redisign on a fairly substantial system to remove the uber-code table because it's proven to be unmaintainable, a performance nightmare, and a source of bad data. It ain't fun.

"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

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 6

Guess I get to put the bat down... 😀 Separate tables.

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

As I said... this can quickly turn into a wholly war :hehe:.

noeld SSC Guru Points: 96590 More actions · Answer 8

This ssssssooooooooo dangerous that "Bankers Rounding" could be in threatened. Apparently we are all in the same boat this time :D:D:D:

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

What was the final post count of that thread??? I lost count after a few 100s! :w00t:

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Wow 376 posts in a single thread... must be the all time record here.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 11

Heh... how quickly you all forget... there were TWO of the bloody damned things 😛

--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)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

This is where you see why I'm still sane >> I wasn't following any of them :w00t::hehe:.

David Burrows SSC Guru Points: 65144 More actions · Answer 13

I did :blush:

bugger! :crazy:

Far away is close at hand in the images of elsewhere.
Anon.

Antares686 SSC Guru Points: 125444 More actions · Answer 14

chris dietz (9/26/2007)
I am in debate with our DBA about storing all codes in one single table or having a code table for each code entity.
A typical system may have 10 to 15 code entities.
Any thoughts?

I know everyone quickly suggested seperate tables, howveer can you be more descriptive of the requirements you must meet and what you mean by code entities? I could tell you seperate tables but if you need flexability and scalability with little effort down the line then a single table may meet your actual goal, but if you have trillions of record for each code and don't mind system maintenance then seperate tables may be more efficient. But based on what you put out I can say profoundly, "not enough information provided".