Your opinion :: multiple databases

Desdinova

Hi,

I'm setting up a new database for a reasonably big webshop. To keep all code and structures pretty clear I was thinking about using multiple databases, each containing their own tables, instead of simply putting all tables into one database.

I don't know, I've never used multiple databases in one application before. Is it good practice? should I avoid it? How easy would it be when a join is required, or should I work around joining by using multiple queries?

Any views are greatly appreciated 🙂

leatherback

I have never had the need for multiple databases, but especially if you now already see that you would need to join tables between databases, I think you are creating more trouble then worth it. Why do you think it would reduce complexity?

Piranha

I fail to understand how the code and structure should be more clear with multiple databases. And I fail to understand how you want to use the different databases, what information should be in one database and what should be in another. If you could clarify ththese points it is possible for me to help.

NogDog

The only reason I can normally see for using multiple databases is if there is absolutely no relationship between the data stored in each. Even then, it's not really a reason to make them separate but rather a reason not to be concerned if they are.

Desdinova

Hmm ok. It's just that in a lot of books I see them selecting all kinds of databases, so I thought it must have been good practice.

I didn't say it would reduce complexity, but it would be visually more comprehensive when opening phpmyadmin for example. I have a project with 20+ tables, and I don't like scrolling. Furthermore, I remember in my old days putting all files for a website in a big dir, and when I became more adept, I moved those to separate folders too. Not because it made things easier, but because it's visually more comprehensive. And that's a bit what I'm trying to achieve here.

In the meantime I've set up all tables, with a category in front of it. I.E. products_quantitydiscounts, products_lastviewed, etc. This makes it visually better as well, and judging by your replies I guess this would be the best way to go.

leatherback

20+ tables screams 'I might have too many', and suggests that maybe you have gone too far in normalization? Something like products lastviewed.. Wouldn't that just be a colum in the products table?

Any way.. There is no need for 'pretty views' is there? In the end you need to build a layer (The php / HTML) that allows people to interact with it. The easiest way to do this is by having all tables in the same database.

happy coding

Desdinova

No since it would be customer specific. Pretty views, when done right, can ease maintenance and updates. And that's eventually the main goal. Looking and understanding, not looking, thinking, puzzleing and then understanding. 🙂

I'll go with underscores for now.

Piranha

What do you mean when you say it would be customer specific? You should use the same database and tables for all customers, handling different customers with relationships.

To get the database right please describe exactly what the application should do and post the database layout you are thinking about. Then it is possible for us to help you out, seeing possible flaws in the database design.

NogDog

I have worked on (non-web) projects with literally hundreds of tables in the one (and only) database for that application, designed by experienced DBA's and System Engineers whom I am pretty sure knew what they were doing (definitely more than I do 🙂 ), so I don't think number of tables should be driving this decision.

While it may sound like a nice way to organize things, if at any point it results in you jumping through hoops to join data between databases, then any such benefit will have been totally negated by the complexity and performance hits you'd take doing such a join. If you are concerned about visibility of data by different customers or such, that's what views are for.

But this is all sort of academic without knowing the precise situation you are dealing with, what all these different tables are, and why they might logically reside in different databases.

Desdinova

well it's more a basic question, when to use different databases. It's not something that will be shown to the users, I just hate browsing through a long list of tables trying to find some table of which I'm not sure what the name is, so I thought using different databases might fix this, if it wouldn't cause too much performance and scripting problems.

Piranha

Then I finally have an answer for you:

When building new applications from scratch use different databases for different applications. For one single application use only one database. It might even be that you should use one database for many applications if the applications use the same data.

For applications that you don't build from scratch it is harder. You might find yourself in a situation where you have to use different databases for one application or rebuild much of the applications already using the databases. In that case you really have to evaluate what will be the best thing to do now and for the future.

I just hate browsing through a long list of tables trying to find some table of which I'm not sure what the name is, so I thought using different databases might fix this

That is lazy talk, and shows that you don't do the job properly. You should always design applications and databases to start with, otherwise you will get into problems later on. Do it on paper with enough descriptions and you won't have a problem knowing what the different tables and columns are.

NogDog

Also consider that naming conventions can be used to organize tables within the same database. If a group of tables are logically related to each other, you could name them in a manner that reflects this, perhaps starting each name with the same word(s).

Sxooter

The real discussion here is about namespaces. In Oracle and PostgreSQL you have namespaces called schemas within a single database that let all your various data sets live in the same database without bumping into each other.

In Oracle they're built on a one to one relationship to usernames. If you have users joe, jim and jack, then you have schemas joe jim and jack.

In PostgreSQL they're more freeform, you can create schemas and users completely separately.

Syntactically, you references schemas in those dbs like you reference dbs in mysql.

select field1 from schema.tablename yada...

In Oracle you have one current schema and all other objects must be referenced with a schema prefix.

In PostgreSQL you have a "search path" of schemas to look in. This allows you to cool stuff like:

create schema base;
create schema customer1;
create schema customer2;

Put common tables like product name and product id and inventory data into the base schema. Now put customer specific data into the customer schema. When operating on the data for customer1 you have a search path of 'base','customer1' and for customer2 you switch the second one there.

Then, your queries that would require fully qualified names with Oracle (or separate dbs in mysql) and look like this:

select base.prod_table.prod_id, base.prod_table.prod_name, base.prod_table.qty, customer1.cust.id, customer1.cust.yada join more yada...

becomes just

set search_path='base','cusomter1';
select prod_id, prod_name, qty, id, yada from prod_table join cust on (...

The real advantage to schemas over databases is that usually schemas are faster than separate databases. But I'm not so sure for MySQL is there's a lot of overhead on having separate databases.

Ah well, I'm just rambling now.

Desdinova

@
thanks. Unfortunately I often work on applications, which later on have to be upgraded with new features which were previously not an option. Furthermore, I don't get the time to document that much.

@
I'm using tablenames with an underscore, ie products_lastviewed. This way it's ordered alphabetically, and all pre-underscore words are listed together. This should work fine.

@
Thanks for the reply, but I think that's something else. The queries are not the problem. It is the database management tool (which in my case is phpmyadmin). The tablenames only give that much information, sorting them in different databases should give a shorter list of tables that might contain what I need to find. But as nogdog said, I just make sure they start with the same word to group them.

Piranha

I just have one more comment to make. No developer that I know of have time to document. I sure don't. But it doesn't stop me, I know that in the long run I will save time by documenting. The question is of course how much to document, and there is no easy answer.

Desdinova

I document a little. DBstructures and such are all worked out forehand. However, when looking back at certain code after a few months, I have trouble remembering where to find what, and although structures have been documented, finding the document and deciphering it usually takes its time as well.

does any of you guys use the extremeprogramming method? I'm trying to pick this up, might give me some help with documentation in a more technical layout.

laserlight

does any of you guys use the extremeprogramming method? I'm trying to pick this up, might give me some help with documentation in a more technical layout.

If you mean the "test driven" aspect of XP, then yes.

NogDog

Since I'm normally a one-person "team", I have not yet tried the XP pair programming concept. 🙂

laserlight

Since I'm normally a one-person "team", I have not yet tried the XP pair programming concept.

I thought that Nog and Dog make a pretty good programming pair 😉

NogDog

Noggin can't work the keyboard with his paws, and the voice recognition software has trouble differentiating "Arf!" from "Arff!".