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 |
|
sproket
Starting Member
1 Post |
 Posted - 2010-10-06 : 03:49:14
|
| Hi,I am new to designing large databases. I could use some advice regarding the best database structure.We are in a database design plan, so how would be the best way to do this.I have a table, called transactions and this table holds all financial transactions within a company.Our plan (dream) is to have thousands of companies on the system, that each has 1,000-5,000+ transactions, so that means we can potentially have up to 25,000,000+ records in the table.My questions is – should I have one table for all companies ie:table.transactions – holds all 5,000 companies * 5000 transactions = 25,000,000 recordsor should I give each company their own table ie:table.transactions_company_0000001 – holds company 1 and 5,000 transactions (records)table.transactions_company_0000002 – holds company 2 and 5,000 transactions (records)table.transactions_company_0000003 – holds company 3 and 5,000 transactions (records)etc. * 5,000+ tablesIf I give each company their own table it would also be more secure I think, but the administration of the tables will be more difficult as if I need to add a field or make changes, I need to go through all tables or at least program a script that does it.Is it possible for each table to inherit a scheme or something, so I only need to make changes to one table.What is the best way to do this, performance wise and secure wise.Any feedback is very much appreciated.Kind regards,Kim |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-10-06 : 04:58:30
|
| no to "table.transactions_company_0000001".why not a seperate database for each company...with identical data structures in each?plus master database to potentially hold central/shared data structureslot of upside ...some downsideup. inter-company security improved - A can't see B, etc load-balance options available....A on server1, B on server2, etc upgrade time - can drip feed upgrades in line with customer requirements standard code - no customisation nor dynamic SQL specifically required less dynamic SQL SQL => improved database security.down upgrade time - loads of database versions need to be updated compared to all-in-one-database option.above not exhaustive |
 |
|
|
Jasons
Starting Member
2 Posts |
Posted - 2010-10-20 : 16:54:19
|
| SQl server would be easily handle 25 million rows in one table, when the indices are approprite and the fragmentation is not too bad.We have a producion table which now has 40million row and we haven't seen any problem with that.I don't think you use seperate tables/databases for different companies is a good idea as you could make a hard life for developers.J.S |
|
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2010-11-25 : 08:17:29
|
| no need to create seperate table for each company.you can maintain all the companies in one table.but remember proper indexing must be done for good performance.u will just refer the company_id where ever required in your transactions. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-11-25 : 08:33:09
|
| No offence Andrew but the suggestion of having a separate database for up to 5000 companies probably isn't very well suited in this situation, at least not to my understanding. Having them all in the same table seems like a good way to go. SQL Server easily handles 25 mill rows and even 250 mill rows should your business go booming. Well..."easily" would be a matter of definition but as long as you're organizing data properly you'll be fine for a very good while.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2010-11-25 : 12:02:21
|
| No problems from me with the alternative view. If I was a customer of OP running I business using this service I would prefer to be as unaffected by other users as could be. Both from a security point of view and from a database integrity/restore point of view. I understand and have no problem with SQL being strong enough for "volume"....but being self-contained would be higher up my score chart if I was evaluating the service on offer in any potential RPF. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-25 : 12:11:28
|
| Depends on the relationship with the companies.If they are accessing their own data then you probably would want to segregate the large companies into their own databases (or servers now or in the future). Keep the small companies all in a single database and table.If they are delivering data for you to use then a single database should be fine.If you have an sla with each company and one company could affect another then you might want to keep them separate as much as possible.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Database design