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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-08-23 : 16:14:04
|
| This is a question on database design for a "web-based course" offered to multiple (smallish) companies with (small) numbers of users ( hence small amounts of data ).A simple approach, and maybe the best, would be to allocate a separate database for every company. Separation optimizes security, and also the effects of a db failure. It maximizes the number of databases and there are some headaches associated with that. The ASP code is more straightforward than the following solution.A more complex approach: single database for multiple companies segrated by ownername security and unique company-prefaced tablenames.companyA_Table1companyB_Table1Key benefits of this approach are fewer unique databases to be managed.A third design of sharing tables and having a column identifier for the company leaves me with an uncomfortable concern of programming errors mixing company data. I've about ruled out this design.In my view, neither of the first two designs is a clear winner. I may opt for separate databases just so the SQL is more readable (tablenames won't be calculated).I'd be interested in any other views or experience-based stories that may shine new light on issues I haven't considered. Or maybe someone knows a better design?SamC |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-08-23 : 18:49:40
|
| Sam, you've hit on the basics pretty well. With the multiple databases, remember that part of that management headache is deploying any updates to stored procedures and table structures. Will it be okay to have some updated and others not? How much headache would that cause? What happens if you skip one? Will your clients be tempted to ask you for a custom feature that only they have or need and now you have multiple versions you're tracking, etc.But before all that, I would ask how important is it for this data to be segregated. When you say "web-based course" are you talking about web-based / computer-based training? Is this a service you are providing to the client companies? What kind of data are you collecting? To what degree will they need to view this data? What sort of programming errors could "mix company data" and how bad would that really be? Are we talking super-confidential financial data, or just class grades?Whichever approach you take, I would STRONGLY encourage you to NOT go with the company-prefaced table names. I feel a migraine coming on just thinking about the headaches that would lead to. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-08-23 : 20:49:40
|
Mark,Thank you very much for your comments. ( I think ! ) I hadn't considered the impact of your projections of table updates, procedure updates, lock-step execution of the updates, update the web code - do it all after hours (caffine, pizza, sci-fi) - as opposed to the simplicity of updating a single database. Now I'm clear that a single database is the way to go. ;-)quote:
Whichever approach you take, I would STRONGLY encourage you to NOT go with the company-prefaced table names. I feel a migraine coming on just thinking about the headaches that would lead to.
Hmmm.... "Context driven" code is nothing new. Context-based table names struck me as a reliable proposal for establishing context. The strength of this solution over a "column" based context identifier was the failure behavior when a programming bug results in a null or incorrect context. It isn't likely to impact anything as a null or misspelled company name shouldn't touch any other table.A good example of this would be INSERT. If table names are fixed and context were written with the data, then INSERT would probably succeed with a NULL or misspelled company context. Constraints could be applied to prevent this occurance, but then this could get messy to maintain as more companies are added.. INSERT would fail if the tablename established the context.------------WRT how important is the segregation? It's vital. Administrators from every company need to pull completion reports on their employees. I really don't want hiccups to cause boundrys on company data to blend.( As I write this, I realize that updating tables / procedures on multiple companies with separate tables in the same database is only modestly less difficult than separate databases.)Seems like separate databases is the safer choice... - Better segregation - Better security - Better behavior in the event of a DB crash - Easier ASP programming - More scalable in performance (DB can be moved to another server)Downside - More difficult to apply table updates, procedure updatesSamC |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-08-24 : 00:17:36
|
| Hi SamI have built similar systems to what you are talking about and the option I suggest is having a common database, with common tables partitioned by a client ID in all relevant tables. I know it isn't the one you want to do, but it works very well.All my data access was done with stored procs and pretty much every proc had a client ID parameter in it. This way, even by changing URL paramaters in the ASP pages there was NO chance of seeing anyone elses data.This method will take a little more work upfront, but you will save it hundreds of time over when it comes to maintainance and management.Hope that helps a bitDamian |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-08-24 : 11:44:53
|
| Thanks Merkin,I'm coming to understand (believe?) that the optimal design approach (one DB or multiple DBs) is a question of numbers (of clients) and size (of data). If I have a huge database for a mega-client - no problem. He wins a database. If I had one... to a 'few' small customers - again no problem - give them each a database. When the numbers of clients is large (with modest to small data).. - gotta design a single database.I don't doubt the context driven stored Procedure you mention is the way to go. Passing the 'context' to a procedure - whether it's a 'ClientID' or a "CustomerName". Implementing it as a Column or a TableName prefix is less clear to me and I doubt one of these designs bests the other for all situations.Using Client-specific table names to establish client context would not introduce extra runtime query conditions. The resulting INSERT, DELETE, UPDATE(s) would not reflect any executable conditions about which client context is in question. The resulting querys should be easier to read, and of lesser importance, execute faster.Tradeoffs, tradeoffs - considering the impact of having to evolve the design after deployment by ALTERING a table. The impact on the multiple table design is not good.I'd be interested in seeing an example of how you implement ClientID in your DB tables. I imagine it adds a query parameter 'ClientID' to every INSERT, DELETE or UPDATE etc, making the SQL logic (somewhat) more complex because client establishment is added to every query. 'VIEWS' come to mind as a way to hide the context component of the query CREATE VIEW UsersASSELECT * FROM UsersAll WHERE ClientID=@MyClientIDGOso the SQL becomes more readable - but this would depend on having a client context variable that the view could reference. This context would be passed from the ASP code to the procedure which would define the session variable - I don't know of a 'session' variable to achieve this in VIEWs.It seems optimal to develop a "multiple" client DB design which should perform just fine when deployed to support a single client. This benefits us as we deploy, support and evolve one design (not two), gain "UP" scalability (dedicate a large customer to a single DB)and "DOWN" scalability (dedicate a single DB to lots of small customers), maintain one set of procedures for both large and small clients etc.SamC |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-08-24 : 11:59:43
|
| HiLike I said before, I used procedures for all the data access, so they all had a client id parameter. So yes, all the crucial tables had a client id column in them.It does make the queries a little more complex, you sometimes need to join an extra table or two. But the maintainence tradeoff is worth it.If you do get a mega huge client with heaps of data, you can move their data someplace else and set them up with a database that only keeps their data. (i.e. all the procs will have clientid = 1)The thought of client prefixed tables scares me a whole bunch, I think you are entering a world of pain if you do it like that. Not only from a maintainance perspective, but also because you will need to use dynamic sql for everything in your up....unless you want to write the same stored proc 5 times......shudderDamian |
|
|
|
|
|
|
|
|
A question of cost and complexity