-1

I have a co-worker that creates a new database for every CONTROL (that's right, I said CONTROL) on a website that we both develop for. My workplace purchased a portal from a vendor and my co-worker and I develop custom controls to work inside the portal. However, my co-worker and I have been butting heads.

No place that I have ever worked created a new database for every control created on a portal site.

He creates a new database (not a new table) for every control he creates on this portal. His reasoning is that he wants to isolate an issue if one were to occur and if the database were to be compromised then it would be isolated to that one controls database.

That sounds good, but I believe it causes other problems such as: - He Replicates tables and data from other databases - Managability of duplicated data - If any of the controls pull data, it doesn't guarantee it's the latest and could cause calculation issues in other areas esecially if it's a financial control - Upkeep of the tools that do the update of the duplicated data - Multiple instances will consume more overall ram and storage - Multiple instances will create more monitoring - Multiple instances will create more administration problems (maintaining new user accounts) - Multiple instances on a box will be harder to tune - Maintaining DATA INTEGRITY is easier with a single database

This guy has the same job title and responsibilities as I do, but I don't have the power to tell him to change his ways.

MY QUESTION IS: Is there a industry standard or rule that says not to create a new database for each control on a site?

Frustrating situation...

user3108867
  • 7
  • What is a "Control" on a web site? – Gordon Linoff Jan 21 '16 at 14:43
  • 1
    Hello and welcome to StackOverflow. Please take some time to read the help page, especially the sections named ["What topics can I ask about here?"](http://stackoverflow.com/help/on-topic) and ["What types of questions should I avoid asking?"](http://stackoverflow.com/help/dont-ask). And more importantly, please read [the Stack Overflow question checklist](http://meta.stackexchange.com/q/156810/204922). You might also want to learn about [Minimal, Complete, and Verifiable Examples](http://stackoverflow.com/help/mcve). – Morgan Thrapp Jan 21 '16 at 14:45
  • @GordonLinoff A control on a website is like a button or a drop down list. – Tab Alleman Jan 21 '16 at 14:45
  • In general use Occam's razor: `"Entities must not be multiplied beyond necessity"`. Show example of `control` and corresponding `db` – Lukasz Szozda Jan 21 '16 at 14:45
  • Yea, tell us more about the technologies you're using that way we have context. Is this ASP .NET? – arjabbar Jan 21 '16 at 14:45
  • We use ASP.net so when I say a control, I mean a web user control (ascx) in ASP.NET. – user3108867 Jan 21 '16 at 15:03
  • We are also using sql server. Please pardon me. I don't normally post questions. I normally just search for answers. – user3108867 Jan 21 '16 at 15:09
  • I disagree that this is a matter of opinion. I think Microsoft has pretty good guildelines on what servers, databases, schemas, and tables are and how they are used. There might be some opinions in many cases, but this usage seems to fall so far outside the norm that it does not seem like opinion. – Gordon Linoff Jan 21 '16 at 15:14
  • We develop with Visual Studio. – user3108867 Jan 21 '16 at 15:19
  • I agree Gordon. I don't believe this is a matter of opinion either. – user3108867 Jan 21 '16 at 15:20
  • Does anyone know where the guideline is that basically says what Im looking for? reason I ask is because if I can provide that to our boss then we might be able to influence my co-worker to change his ways. My co-worker is a very smart person, but very hard to convince to change – user3108867 Jan 21 '16 at 15:22
  • I don't think there's a specific guideline for controls to databases. But the industry standard for designing application databases is pretty well understood - the fundamentals of database design are not contentious. I'd start with comparing your colleagues design against normalization standards, e.g. BCNF – Neville Kuyt Jan 21 '16 at 16:15
  • Ahh, that's good advice.. I will definately do that. – user3108867 Jan 21 '16 at 16:27

1 Answers1

1

In general, keep the following in mind:

  • Servers are the unit of connectivity (an application connects to a server).
  • Databases are the unit recovery (and hence backup) and reside on servers.
  • Schema are the unit of security and reside in databases.
  • Tables hold data and reside in schemas.

I don't fully understand what a "Control". However, it would be appropriate to create a database for one if you had different recovery mechanisms needed for it. This would be the case for different applications. There might be other considerations, such as visibility of data, stored procedures, and the like, as well.

Otherwise, it should probably be supported in one of these ways:

  • All the data in a single table.
  • Multiple tables in the same schema.
  • Multiple schemas, one per control.

Your colleague's concerns about security are admirable, but different databases are not the right solution. If you have a DBA, then you should talk to the DBA about directly encrypting the data on disk -- so even backups are safe without the key. Also, security violations are as likely to occur at the server level as at the database level, and storing data in different databases doesn't help. It might, in fact, put you at risk unless you have a very intelligent recovery/backup strategy.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • We use ASP.net so when I say a control, I mean a web user control (ascx) in ASP.NET. – user3108867 Jan 21 '16 at 15:02
  • @user3108867 . . . I should add that there are significant performance issues with creating a database that are not factors for schemas and tables. – Gordon Linoff Jan 21 '16 at 15:15
  • Ahhhh, that's a good point. Gordon Linoff, Do you know what kind of performance issues? – user3108867 Jan 21 '16 at 15:26
  • @user3108867 . . . Databases consist of files, which have to be created by negotiating with the operating system. This incurs more overhead than writing some metadata/small amounts of data to an existing database (which must also be done). – Gordon Linoff Jan 22 '16 at 03:31