4915RE: [SQLQueriesNoCode] Better approach to Queries and Relationships design Question
- Jun 26, 2014
I would only separate the data into two tables IF:
1. A set of the columns are retrieved significantly more often than the remaining columns
2. A set of the columns are often NULL (then it would be a one to less than one relationship)
3. There are few requirements where I need to join the two tables.
Separating into two tables, one with FKeys and other lookup values to facilitate JOINs, filters, etc., is meaningless since all lookups and JOINs will be using indexes, not the tables.
Especially for DW use, I would not separate the data. Often in DW designs, we will ‘flatten’ and denormalize multiple tables in order to enhance query responsiveness. (Normalization is for storage and data consistency, not query responsiveness.)
If using SQL Server, and some columns are most frequently NULL, consider using SPARSE COLUMNS and COLUMN SETS.
Arnie Rowland | Westwood Consulting, LLC | +1-503-246-6172
Microsoft SQL Server MVP
"You cannot do a kindness too soon because you never know how soon it will be too late."
-Ralph Waldo Emerson
I'm not a DB authority, so maybe I'm missing the point here. What are you trying to accomplish by splitting up a single record?
Let say you have a simple database like this:
ID Quad FKDept Est St FKPer Budget
1 North 88 1973 MD 990 7,238,123
2 South 77 1978 CA 123 9,876,902
3 West 12 1984 MO 479 19,876,902
etc. with some additional Data after Budget....
My inclination would be to setup 2 tables
ID FKsample Quad FKDept Est St FKPer
1 777 North 88 1973 MD 990
2 145 South 77 1978 CA 123
3 84 West 12 1984 MO 479
ID Budgeted Field2 Field3 Field4
rather than store every thing/foreign key value that could be queried
with the data.
Am I "smelling the right direction for the solution?
Or is the top table fine, and the 2 table approach bad?
I did not do the example fully normalized with domain tables mainly to
illustrate the concept more clearly. The final implementation of
LinkSample would be mostly Foreign keys (Quadrant, etc.)
I have encountered quite a few tables like the one above and I prefer
the second approach, from querying, design, and SOC/Simplicity
standpoint but maybe I am wrong. So I was curious about the design /
data warehousing / BI considerations from the experts here.
- << Previous post in topic Next post in topic >>