column count per table best practice
- Jeff Moden wrote:
Heh... so THAT's the reason why so many people end up with "slow databases" and IDENTITY columns everywhere. 😉 A lot of the important stuff isn't even considered up front.
I think so ;).
Worse, many, many people start "designing" tables by first assigning an IDENTITY as the "key". Then they claim to "normalize" -- but it's literally impossible to normalize based on an IDENTITY.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
It would be really interesting to see you design a Customer or Employee table without one. I'd also be interested in your claim of "it's literally impossible to normalize based on an IDENTITY" for such 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) - Jeff Moden wrote:
It would be really interesting to see you design a Customer or Employee table without one. I'd also be interested in your claim of "it's literally impossible to normalize based on an IDENTITY" for such tables.
Wow, you really q that?! You believe you can normalize using an identity??! I'm stunned. The essence of normalization is that "every non-key column must be dependent on the key, the whole key and nothing but the key". But how does that have any real meaning if your "key" is a meaningless number? I gotta say, it doesn't seem as if you've much logical modeling at all.
That said, on the physical side, you'd almost certain use an IDENTITY to assign the customer_number. I certainly would.
Going further, though, for the direct children of the customers table, I would not use identity as the leading clustering key. I'd definitely use the customer_number instead, adding an identity or address_code or whatever column was best for that table to insure a unique clus key.
As another example, the orders table should most often be clustered on an identity. But the order_items should not. Again, cluster it on ( order_number, <whatever> ). Vastly better joins. And those two tables will be joined all the time. And it's not like the order_number is wildly variable: it itself is an identity, thus it will be nearly sequential in the _items table as well.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
That's where you and I would differ... to me, the "customer number" would be the IDENTITY column. Or, perhaps, a Random GUID. 😀 Why would your "customer number" not be a completely meaningless number?
--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) - Jeff Moden wrote:
That's where you and I would differ... to me, the "customer number" would be the IDENTITY column. Or, perhaps, a Random GUID. 😀 Why would your "customer number" not be a completely meaningless number?
I think the issue is trying to normalize on the identity itself. In the logical design you create an attribute called 'customer number' - how that is implemented doesn't matter at this point. The point is that you have identified the 'key' to that entity type and all other attributes follow.
The 'customer number' could be an identity, and application generated number, a GUID or some other methodology for assignment.
At least, that is how I would think of it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
deleted!
- Jeffrey Williams wrote:Jeff Moden wrote:
That's where you and I would differ... to me, the "customer number" would be the IDENTITY column. Or, perhaps, a Random GUID. 😀 Why would your "customer number" not be a completely meaningless number?
I think the issue is trying to normalize on the identity itself. In the logical design you create an attribute called 'customer number' - how that is implemented doesn't matter at this point. The point is that you have identified the 'key' to that entity type and all other attributes follow.
The 'customer number' could be an identity, and application generated number, a GUID or some other methodology for assignment.
At least, that is how I would think of it.
I'm really looking forward to Scott answering my questions and why he thinks that a CustomerNumber can be "normalized" when an IDENTITY cannot.
--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) - Jeff Moden wrote:Jeffrey Williams wrote:Jeff Moden wrote:
That's where you and I would differ... to me, the "customer number" would be the IDENTITY column. Or, perhaps, a Random GUID. 😀 Why would your "customer number" not be a completely meaningless number?
I think the issue is trying to normalize on the identity itself. In the logical design you create an attribute called 'customer number' - how that is implemented doesn't matter at this point. The point is that you have identified the 'key' to that entity type and all other attributes follow.
The 'customer number' could be an identity, and application generated number, a GUID or some other methodology for assignment.
At least, that is how I would think of it.
Jeff Moden wrote:Jeffrey Williams wrote:Jeff Moden wrote:That's where you and I would differ... to me, the "customer number" would be the IDENTITY column. Or, perhaps, a Random GUID. 😀 Why would your "customer number" not be a completely meaningless number?
I think the issue is trying to normalize on the identity itself. In the logical design you create an attribute called 'customer number' - how that is implemented doesn't matter at this point. The point is that you have identified the 'key' to that entity type and all other attributes follow.
The 'customer number' could be an identity, and application generated number, a GUID or some other methodology for assignment.
At least, that is how I would think of it.
I'm really looking forward to Scott answering my questions and why he thinks that a CustomerNumber can be "normalized" when an IDENTITY cannot.
Exactly Jeffrey. An identity can never exist in a logical model because it's a physical implementation. A customer number is a logical abstraction. More details on all this later.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
BWAAAA-HAAAA-HAAAA!!! Ok... I "get it". Yep... I agree... you never have to mention "IDENTITY" in a logical model. I have to tell you, though, that the idea of having a logical model with boxes on it that necessarily imply "Insert Miracle Here" is something that doesn't fly with me even in a purely logical sense and that was what my original argument was meant to be. Design all you want... but no matter what you design, you still can't use "cold fusion" yet so don't imply it in your design. 😀
--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) - Jeff Moden wrote:
BWAAAA-HAAAA-HAAAA!!! Ok... I "get it". Yep... I agree... you never have to mention "IDENTITY" in a logical model. I have to tell you, though, that the idea of having a logical model with boxes on it that necessarily imply "Insert Miracle Here" is something that doesn't fly with me even in a purely logical sense and that was what my original argument was meant to be. Design all you want... but no matter what you design, you still can't use "cold fusion" yet so don't imply it in your design. 😀
Again, you haven't ever done an actual logical design, have you? Genuine data modeling, with no indexes, etc.., where you went through a true normalization process.
You don't need miracles, you just need to follow the rules. And the required steps.
A customer number is an added, artificial candidate key but it is not necessarily the only candidate key. The data would need to follow the rules regarding all valid candidate keys.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:
Again, you haven't ever done an actual logical design, have you? Genuine data modeling, with no indexes, etc.., where you went through a true normalization process.
Heh... more than you will ever know. Here's one of the smaller examples of a "system design" that I created so others will get it. Heh... notice the absence of the word "IDENTITY" or even a "Customer_Number" 😀 (sanitized for display here)...
--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) - Jeff Moden wrote:ScottPletcher wrote:
Again, you haven't ever done an actual logical design, have you? Genuine data modeling, with no indexes, etc.., where you went through a true normalization process.
Heh... more than you will ever know. Here's one of the smaller examples of a "system design" that I created so others will get it. Heh... notice the absence of the word "IDENTITY" or even a "Customer_Number" 😀 (sanitized for display here)...
Not a data model in any way or form. Yeah, it's cute and all, but it's 100% irrelevant to data modeling.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Heh... ah... I know what you mean now. No comment. 😉
--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)
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply