October 9, 2007 at 1:52 am
HI All,
I was wondering if someone could answer my question about designing tables with integers that represent bit values.
I think I know the many reason for not doing but not 100% sure on the performance issues.
Lets say I have a client table (tblClient) with a field called SystemID (INT)
Which is a better method of implimentation?
NUMBER 1:
Having a lookup table with the following values:
ID - System
2 - Sys1
4 - Sys2
8 - Sys3
16- sys4
etc
Then on tblClient in the system column you could have values such as:
2 = Sys1
14 = Sys1,Sys2,Sys3
18 = Sys1,Sys4
etc
NUMBER 2:
Having a lookup table like the following:
ID - System
1 - Sys1
2 - Sys2
3 - Sys3
4- sys4
etc
Having an intermediate table like the following:
ID systemID Client
1 1 123
1 4 123
1 2 123
Which is a better method?
Here are the pro's that I have come up with:
NUMBER 1:
Less deletes and insterts into the database
Quick ability to tell which systems are being used
Les tables needed
NUMBER 2:
Keeping your Database relational
Ability to have FK's
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
October 9, 2007 at 7:22 am
Most systems do some form of option #2. The main reason being, reporting. Your basic report engine just isn't going to be able to figure out the bit mask the way you do in VB code. The other reason you already mentioned and that's maintaining data integrity. It's not simply a matter of having a foriegn key, it's enforcing a particular set of data so that your data is in a known state. Nothing would prevent the option 1 from getting a value such as 5 or 7, without putting additional constraints and possibly triggers in place to ensure the correct methods are adhered to.
Honestly though, it depends on what the data is and how it will be used. As always, the first and final answer is, it depends.
"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
October 9, 2007 at 7:39 am
I think Grant has given a good explanation of why #2 works. It's easier for the next person (developer or user) to understand as well. It makes client development slightly more complex, and the big thing is we don't need to.
We're not space or resource constrained like we were in previous eras where disk, network, and CPUs cycles were relatively expensive.
Relational makes things a little more controlled. Bit masking was done initially to save space (bitx v bytes).
October 10, 2007 at 11:12 am
don't forgetting indexing (a strong + for #2). you'd be relegated to row scans/table scans to find the ones you want (for a where clause, for ordering, etc....)
Finally - it's not really scalable. What happens when you end up with 65 systems?
----------------------------------------------------------------------------------
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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply