Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bcsmith100
Starting Member
4 Posts |
 Posted - 2006-11-13 : 09:51:34
|
| I learned database design informally from while being a coder on Projects back in the 1990’s. Back then (as I remember) when weHad very large database with the multiple table, multiple part keysWe handled it as follows because we found the databases wouldScale better.Here is the problem I have and how I plan on implementing the keysAnd I want to know if this is the best way for the databases to Scale for large Db and queries. Table A------------A_ID (PK)Table B-----------B_ID (PK)A_ID (FK)TABLE C-------------C_ID (PK)B_ID (FK)A_ID non-key, no ref. integrity build in, but I handle integrity on inserts.TABLE D (Link Attribute table)-------------D_ID (PK)B_ID non-key, no ref. integrity build in, but I handle integrity on insertsC_ID (FK)E_ID (FK)TABLE_E -------------E_ID (PK)For queries will often want to show all of the quantities for each table for aGiven value in table A. My thoughts is that with this structure I optimizeThis query path, cutting down on long multi-path joins AND HOPEFULLYMAKING THE QUERIES MORE EFFICIENT FOR VERY LARGE DATABASES.But is this true? |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-11-13 : 12:21:27
|
| You don't make a very large database more efficient by cutting down multi-path joins. The multi-path joins are actually what enables them to scale larger. Remember, you are trying to normalize the data to best take advantage of the relational aspects of the data. The placement of the keys should depend on the relationships of entities together, as defined by:1. Natural data relationships.2. Application or business process logic (generally represented in join or matrix tables).People often try to denormalize through bunching keys together, or worse just denormalizing all the data, so things can scale better. The appearance is deceiving because initially some of their queries and reports will run faster. Later, they find themselves in big trouble though.Remember, you are dealing with a RDBMS. If you are having performance issues, it might be an issue with not thinking set-based. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
Bcsmith100
Starting Member
4 Posts |
Posted - 2006-11-13 : 22:27:01
|
| This is not obvious to me. Referring to my example. I will have multi-table joins, but I carry around a few keys in tables as regular columns so that I cut down on the joins, but you say that this is not needed? That I can just join across 4 tables and still get fast queries when dealing with millions of rows in the database returning 100,000 records?How does this lead to problems? (denormalizing...are you referring to duplicating of data getting out of sync?) |
|
|
|
|
|
|
|
|
muti-table design