| Author |
Topic |
|
chaprider
Starting Member
3 Posts |
 Posted - 2007-04-12 : 11:03:50
|
| Hello:My client has a db with the following structure:Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.thxrob |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-12 : 12:05:10
|
quote:
Originally posted by chaprider
Each County table has 10-20 tables.
What does that mean?e4 d5 xd5 Nf6 |
 |
|
|
chaprider
Starting Member
3 Posts |
Posted - 2007-04-12 : 13:05:25
|
| Currently every County has it's own database which contains 10-20 tables of similar structure to other counties.Example: Texas Counties: DB1- Harris County: 10 tables DB2- Fort Bend County: 15 tables DB3- Galveston County: 20 tablesWould it be better to design 1 database. For Example: StateTable(Containing allStates) CountyTable(Containing allCounties) CountyRecords(Containing similarData) |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-12 : 14:03:50
|
| The current design, shall we say, distasteful. But you already know that or you would not have asked about redesigning it.What kind of data is stored in these 10-20 extra tables? Some examples, please? Are the extra tables necessary for normalization, or are they implementing one-to-one relationships with their counties?e4 d5 xd5 Nf6 |
|
|
|
chaprider
Starting Member
3 Posts |
Posted - 2007-04-12 : 15:12:08
|
| They have no relationships. Don't ask me why. Ha!Ha! That's why I would like to re-design the DB architecture so they will have some data integrity when they move to SQL 2005. This is what I'm thinking, but I'm no expert...Table1: (1-1 for Table1-StateCode:Table2-StateCode)StateCode, StateTable2: (1-many for Table2-CountyCode-Table3-CountyCode)CountyCodeStateCodeCountyTable3: (1-many for Table3-DocID-Table4-DocID)CountyRecordIDCountyCodeDocIDTable4DataFieldIDDocIDDataFieldetc,etc...Table Example would be: Table5DocID, LName,FName, Title, DateAdded, etc.Table6: LotID, DocID, Lot, Block, and so on...Thanks for your help! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-12 : 17:01:54
|
Consider that if you have multiple databases, and you have some sort of "disaster", it will be harder to Restore to a consistent state that if you only had One database and One backup file ...... that's not a reason for NOT having multiple database, but it can be something which is overlooked until the first real disaster  Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-04-12 : 17:09:16
|
| I still don't get what you are trying to do, and since at this point in my career I have seen just about everything, this is not a good sign for you.Can we start by giving your tables descriptive NAMES? I'm guessing this:Table: StatesStateCode, StateTable: CountiesCountyCodeStateCodeCountyTable: CountyDocsCountyRecordIDCountyCodeDocIDTable: DocumentFieldsDataFieldIDDocIDDataFieldetc,etc...Which leaves me wondering where the Document table is.e4 d5 xd5 Nf6 |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2007-04-13 : 04:03:42
|
| Disregarding the business needs that you may have (privacy, cost of recoding, etc.) and for technical consideration, I suggest you use only use one database. KISS-- keep it simple. 500MB is not a lot. You can use views if you have to. Your concern about Region, States or other geographical units can be address if you properly model/normalize the tables. Check out this site. There are tons of information regarding normalization and data modeling.Hope this helps.May the Almighty God bless us all!www.empoweredinformationsystems.com |
|
|
|
|
Database Design - Multiple vs. Single Database