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 |
|
Visca
Starting Member
2 Posts |
 Posted - 2009-05-09 : 22:07:39
|
| I'm working on a final project for school and I have a problem with the number of databases.I only have a month for this project, and the teachers made a change on it (to see how we can adapt to an started project. On real world most of the work we go to do is develop an started project) and only three weeks left.Initially we must make a win32 application to administrate a web. Now we have to do a win32 application to administrate similars webs. I have to decide about make only one database for multiple webs or multiple databases. We discussed (we are a team of 5 students) why do one or other option.PROS for single database:Less space used in disk (there are a few data equals for all the webs, two tables of 15). The teacher says it's more optimal.CONS for single database:We must to add a new table ("projects") to control the shown data on each web.We must to do a few relation tables betwen the "projects" and the existing ones.The "select"s will increase a lot (like 2 or 3 tables and 3 or 4 conditions to control this. I supose it will take more time to make selects, and then, what you win in space (see PROS of single database) you lose in time.The time to implement.PROS for multiple database:Better performance for security (the rights will work for tables and projects, not the same rights on all projects).Quicker implementation (is already implemented!).Democratically win (3 vs 1, win for multiple databases option), but we have to say in documentation why we choose this decision.We have 5 teacher, 1 thinks this is a good option, 1 thinks the other option is better but this is acceptable, the other 3 don't says anything. Changing to single database will take more time than the punctuation we can earn doing it.CONS for multiple database:1 teacher says the single database is a better option and this is an important point, this teacher is the responsible of teach databases at class, it's only an important point for him, the other teachers will ignore this decision.Also, you might think it's already decided (democratically), and you have nothing to do with this. But anybody of us are secure about this decision. Help us! (We will write in documentation your help as a consultant, so please, give me your name/nick/alias and the price for your help, and your experience (is the guarantee for your response), you will appear on credits XD, but I'm not going to pay!!)Sorry for my bad English and thanks for your help and time!. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-10 : 10:33:46
|
There are good reasons for using a single database, theoretical, practical and real-world:1. If the data model is fixed, then the database is storing the same domain of data, therefore it should be stored in one place.2. More databases mean more administration hassles, not less. It won't impact the developers as much, but someone will need to manage backups, code deployments, upgrades, database access, etc. It's not a big deal for a college assignment, but it will come up in a work environment.3. More databases also make it harder to consolidate data for data warehouses, reports, etc. Things that no one is asking for now, but will later on.4. Real-world development, as your teacher pointed out, requires change and flexibility to change. It also requires doing the best with resources that cannot be changed or redesigned. Consider this your first test of dealing with this situation, you will encounter it again on the job.quote:
CONS for single database:We must to add a new table ("projects") to control the shown data on each web.
This is minor, certainly less effort than adding multiple databases.quote:
We must to do a few relation tables betwen the "projects" and the existing ones.
Well, this is what relational databases do. This is akin to complaining about a car needing gas and a steering wheel. Fact is, most databases do, and need to, use such a structure, you'll see it on the job for sure.quote:
The "select"s will increase a lot (like 2 or 3 tables and 3 or 4 conditions to control this. I supose it will take more time to make selects, and then, what you win in space (see PROS of single database) you lose in time.
No, not if it's properly designed and indexed. Your application code should NOT pull all data from every table and work on it in application space. Learn how to JOIN tables if you haven't already.quote:
The time to implement.
Granted, but this is (one of) your first project(s), this is the kind of experience you need to address future projects with similar requirements. Next time you get such a request, "We don't need to suport multiple web sites" will be a warning bell for you, and you'll design the database to support it anyway. (Of course, you won't tell the client that, and when they ask for such a feature, you'll sign heavily, say "this will require a redesign", and charge a nice fee to make it look like you're doing it over)  quote:
PROS for multiple database:Better performance for security (the rights will work for tables and projects, not the same rights on all projects).
Peformance and security are essentially opposites, i.e. "high performance security" is pretty meaningless. If your app is good at one, it's weak at the other. Unless security and data isolation are absolute requirements, there's little sense in having multiple copies of the same database. And if you create a structure that can consolidate multiple clients/sites/project in one database, you've done 80% of the work in keeping it secure and isolated from other users.quote:
Quicker implementation (is already implemented!).
True. When you have to support or modify someone else's work who used this as a deciding factor, you may think differently.quote:
Democratically win (3 vs 1, win for multiple databases option), but we have to say in documentation why we choose this decision.
They're making you do documentation? Your teachers are pretty smart.quote:
We have 5 teacher, 1 thinks this is a good option, 1 thinks the other option is better but this is acceptable, the other 3 don't says anything. Changing to single database will take more time than the punctuation we can earn doing it.
Want to blow their minds and probably ace it? Do BOTH designs, and analyze the strengths and weaknesses of both. You say the time to do multiple databases is almost nothing, so put the additional time into the single database design. You've done a lot of the analysis just by making this post, now follow up with actual experiment to prove or disprove these points. |
 |
|
|
Visca
Starting Member
2 Posts |
Posted - 2009-05-10 : 14:24:31
|
Maybe this post will angry you because we will make a multiple database design, only for a time reason. We can't say to client (teachers) "it will take more time".quote:
Originally posted by robvolk
Want to blow their minds and probably ace it? Do BOTH designs, and analyze the strengths and weaknesses of both. You say the time to do multiple databases is almost nothing, so put the additional time into the single database design. You've done a lot of the analysis just by making this post, now follow up with actual experiment to prove or disprove these points.
The time I expend doing this post was at home, theoretically we must do this project in 100 hours, and take work to home is a cheat. Also tomorrow we have to continue working, and we won't expend time on experiments. I will work at home to test it, but I can't write it on documentation!We know that the single database is better, but we want to simulate that we study the different options and we made a bad choice, it's lot different than ignore what teacher recommends us.If it would be a real work project, we can say client:quote:
Originally posted by robvolk
[...]when they ask for such a feature, you'll sign heavily, say "this will require a redesign", and charge a nice fee to make it look like you're doing it over[...]
But we can't, and for this we are going to make multiple databases, we think this change will take more time than the punctuation we can earn doing it.Anyway, we will consider your help (every day I think that single database must win), but due to restrictions of the job (school project) we must choice the bad option (but in a real world project we can got no time to work :S).thanks a lot for your help. Please, put a price for your help:quote:
(We will write in documentation your help as a consultant, so please, give me your name/nick/alias and the price for your help, and your experience (is the guarantee for your response), you will appear on credits XD, but I'm not going to pay!!)
Again: Thanks |
|
|
|
|
|
|
|
|
Pros & cons of multiple databases