My team( all freshers) are developing a project that will involve many Modules.

I want to know if it is a good practice to have different databases for a single project!!! I know that this could lead to a bad design, if you make the wrong decisions of placing the respective tables.

Each database of a module will contain only the module specific tables (that do not need communication to the other tables of other modules), commonly used tables will be placed in a common database.

I want to know the demerits of such a design, cause junking up a database with a lot of tables, seems to be a very complicated system to understand, I mean the documentation part.

Can we relate tables in different databases????

Hi rich_m

>>> My team( all freshers) are developing a project that will involve many Modules.

You must be "sophos ke moros" to carry out such database design successfully, which is every project's most crucial design phase. So wait a year or two until you will have past database design course.

>>> I want to know if it is a good practice to have different databases for a single project!!!

Bad practise, don't do that!!!

>>> I know that this could lead to a bad design, if you make the wrong decisions of placing the respective tables.

No, it couldn't lead to but it is terribly bad design.

>>> Each database of a module will contain only the module specific tables (that do not need communication to the other tables of other modules), commonly used tables will be placed in a common database.

Eh, to make chaos complete, you also plan a "centralised, common" database. Will everybody who programs modules also be allowed to design his module-focused tables himself? That would really speed-up into chaos.

>>> I want to know the demerits of such a design, cause junking up a database with a lot
of tables, seems to be a very complicated system to understand, I mean the documentation
part.

After having collected a billion penalty marks you are going to redesign that datamodel
for the third time. Btw, no matter how complicate a datamodel is, its ERM would be the best-ever documentation for any project.

>>> Can we relate tables in different databases????

Do you mean ensuring referential integrity by means of foreign keys????
Being freshers, you are supposed you cannot do that.

OK, to bring it to an end, I suggest you to peep at a famous sample: SAP R/3 what consists of over 17000 tables. There are companies running R/3 on a single MS Sql Server or Oracle database (Mysql impossible) filled with over 10 Tera-bytes data.

If you don't like to fail again, you should really do correct and complete relational database design where the fundamental goal must be designing tables which satisfy third normal form. Yes, third normal form! All tables must have primary keys. Every relationship must be implemented with correctly defined foreign keys. Never omit them! Never introduce a surrogate key on the quick. Before that you should carefully check the table's columns whether candidate keys exists. If you find one, you should make him primary key.

krs,
tesu

Thanks a million for your time!!!!

Now that I got that clear, will be moving on to the design.....why do u say MySql impossible???? I was hoping that after Sun bought it off.....its gonna crash all the others......cause it is open source + we have Sun brand.

I just found ou that the answer to the Question "Can we relate tables in different databases????" is YES

If MySql has provided a feature to inter relate tables from different databases, i think ther should be some purpose for the same.

I created 2 databases, and managed to suceesfully to a foreign key relation ship by appending the database name.

It really makes a lot more meaning to have this feature!!! It leads to better DB documentaion, my logical perspective about relational tables, is tht it helps keep the data in shape and improves performance. So if MySql provide such a feature, i guess all this is taken care off!!!

Awaiting all your comments.