What is best: separate databases or an extra "code" table?

Question

I have a database designed to hold card data from various trading card games. Up to this point, there has only been one card game I've been tracking, but I'm using an attribute table since not all attributes apply to every card.

I'm now expanding into multiple card games and have a slew of data ready to migrate in. However, I'm not quite sure how to import this data in. Should I simply clone the schema into a new database for each card game, so I can manage things in separate buckets? Or should I simply add a new table called "CardGame" and use the unique identifier for this as a foreign key to the card and have one massive database for it all.

If it matters anything to the situtation, this master database is published to a web site for users to query against, a well as broken down into smaller data modules (per game) for a WinForms application that people can use.

Answer 1

My general preference in these situations is to use one database for the following reasons:

• Comprehensive reporting is made significantly easier
• As with code, DRY applies - if you have multiple 'cloned' schema what happens when you need to make a 'core' change?
• Maintenance is simplified

But the decsion ultimately rests on your specific requirements...

Answer 2

I would be inclined to put each in a separate database. Placing a secondary key on the tables seems easy enough at first, but can really lead to headaches down the road. This is probably especially true since you have already created the db thinking that only one type of game is going to be in it. It's a constant headache to remember in all your queries that you have to reference a second key almost everywhere which shows which game should be accessed/modified. It can be a real night mare. There is an SO podcast where Spolsky talks about why FogBugs has a separate database for each client. (I can't find it in the moment, but I think it is mid-to late 20's).

Modifying the schema for all of them down the road will be a little extra work, but with tools (like ones from RedGate) this is a lot more maintainable.

If you ever think you might have to remove a game from the site, separate dbs will also be a load easier to manage in this situation. Instead of removing all the data from the one large one, you just have to drop the db pertaining to that particular instance.

Reporting off all of the data is also a little more work since they are all in separate dbs. I would aggregate all the data from all the dbs into a reporting database used strictly for reports. Once again a little extra work, but not too difficult.

EDIT: Separating databases is about data integrity. Everyone is talking about how it will be difficult to maintain schemas and update stored procedures. I have programs that cost about 200 dollars that do that for me. Not a big deal.

I'll explain what's hard and I've dread by putting all the databases together.

1. What happens if one of your games needs to change the schema that will break all the others? (or worse, you don't know what will really happen). Like changing the size of a number, or expanding the size of a string, or adding a parameter to a stored procedure. You are pretty much totally screwed. You will spend hours and hours of extra work trying to figure out how one change will affect every other application that touches it. It's like touching a spiderweb. There is no, "We'll just change it for this game."

2. What happens when someone runs an update procedure to fix data because of a bug and forgets to include the appropriate identifier and you update all the data in a table? You bring down everything.

I know what you are saying, "We'll just be careful." That is exactly saying, "We don't need testers, because why would we write buggy code?" Five minutes of extra work to sink schemas, or a couple of extra hours for the programmers review the code in multiple applications (which they should be doing anyway when someone makes a change to a database their application touches) is a joke compared to walking into your bosses' office and explain why you'll be there for the next 10 hours fixing a problem, because someone wasn't totally on their game.

Answer 3

I would use one database:

• Unfortunately, cloned database files are just that - there's no inheritance tree where you could easily make and persist a base schema. If you ever have to refactor or change the schema, you'll be in hot water trying to get all your clones to match up.

• It's easier to maintain and move if everything is in one place.

• You can use views to sort out the relevant parts for each game, as well as each card type. In a well-designed database, the information will appear separate when necessary, yet join together seamlessly.

• As a bonus, you can begin to collect data about the correlation between different games...

Answer 4

I think it really depends on whether the data from the different card games will be used by a single application.

I you have one application that treats data from more that one game than a single database makes sense.

On the other hand, if you logically have one instance of the application for each card game than having separate databases for each makes a lot more sense.

Answer 5

The DRY principle. Don't Repeat Yourself. I believe in following the DRY principle as stringently as possible. Keep it all in one big database. If you just clone db's around, maintenance will be a nightmare, refactoring will be hell, and any future programmers working on the project will be so dumbfounded that they'll probably run faster than Road Runner from Wily Coyote!

Answer 6

Go for one database first of all there is more flexibility maybe some trading card games are from the same company why not choose to split your database that way (this is not a suggestion) if you do that you cannot split them again it is a very inflexible way to organize your data that seems somehow not very compatible with the relational database way of thinking.

Answer 7

I think going multi-tenant as early as possible is a good idea.

If you make the database multi-tenant now, you can still put them in separate databases later if you want with relatively minor changes - the other direction is not as easy. You have to assume in a multi-tenant scenario that the system is going to be well-engineered so that you do not have leakage of information to other tenants. There are a number of ways to enforce this at view or SP level to give good degrees of confidence that coding mistakes can be minimized and mitigated.

You can also distribute the games, several to a database. There are many reasons for such segregation. It probably doesn't apply to you, but for extremely large databases, it sometimes makes sense from a maintenance window perspective to have multiple multi-tenant databases. For instance, by country or by time zone.

In SQL Server, because the outer unit of ACID integrity and backup is the database, having extremely large multi-tenant databases can make it difficult to meet small maintenance windows.