Database Design Help

[ashamsa]

We are working on an application that caters to a group of institutes. With the current database architecture each institute has its own database, with the same structure but data is different. But there has to be a common database that has some data which need not be repeated in each database, like the vehicles owned by the management etc.
But this vehicle may need to be listed or assigned in each institute's database.
We are a group og beginnerss new to this kind of scenario and strucutre.
We would really appreciate if you can be any assistance in solving or implementing this.

Our issue right now is we can list all vehicles from the common database and assign or use that in each individual database.What will happen if someone deletes the data from the common database? How do we check if this data is used in any of the other databases. This kind of situation may arise in future with other entities also other than vehicles. How do we go further? pls help.

regards
ashamsa

[timothybard]

There are different ways you can solve this problem. Below is the solution I recommend:

1) Limit the ability to delete vehicles to only the person administrating the common database. This will prevent individuals from each of the institutions from deleting vehicles.

2) Before the database deletes a vehicle, it checks the data in each of the institutions' databases to see if they are assigned to the vehicle.

3) If an institution is assigned to the vehicle, then do not allow the vehicle to be deleted; otherwise, delete the vehicle

The logic behind the process is straight forward, but actually implementing the solution can introduce issues. For example, what type of database engine are you using? Are all the databases located on the same server? Does the administrator of the common database have access to the institutions' databases? These are all issues that can be overcome, but they may change the method that needs to be implemented.

[ashamsa]

Thank you for your response. We feel we should go ahead with what you suggested. All our data is now in a single server.
I would like to know more about this type of multiple databases and design strategies. Can you pls help me in this regard with some useful links or other materials

[timothybard]

I'd be happy to provide you with more information / links. However, in order to give you more relevant information, I need some additional information from you:

What database are you using? MS SQL server, MySQL, MS Access, etc...
What language is the front end programed in? PHP, ASP, MS Access, etc...
Are there any specific questions you have concerning the method I mentioned above?

[ashamsa]

The database we are using is MS SQL server 2000 and the front end is asp.net.And all the databases will be residing in a single server.
Is going ahead with multiple databases a good way of design? Or would it have been better to keep all the data in one single database and have an institute id with each record to identify to which institute it belongs. When we thought we would use multiple databases, what we had in mind was performance and speed of data retrieval and moving one institute, if need arises to another campus or another server.
regards
ashamsa

[timothybard]

Whether you decide to have all the information in one database instead of multiple databases depends on what you want to do.

From your initial post, I had the impression that each institution already had their own database, which is why I mentioned multiple databases.

I would recommend multiple databases if the databases were located on different servers (which they are not in this situation), if each institution needed to program their own databases (which doesn't appear to be the case in this situation), if there was a need to separate the databases some time in the foreseeable future (which may be the situation in this case).

As far as speed is concerned, I don't think you will be able to see a difference in performance due to the database being a single database; to increase performance, you'll want to spread the database(s) over several servers.

I'd love to hear what other people's thoughts are.

[ashamsa]

Thanks for your reply.It was decided to have multiple databases. But I was just thinking it would have been lot easier if they were a single database. But we find it too difficult to manage somethings like student data is in each individual databases, but their transport requirements has to be managed by the common database. It is just our inexperience that make us worried like not able to set the relationships etc.

Any help/ideas or examples on this matter is most welcome..

[timothybard]

Here is a link to an article about Linked servers in MS SQL Server. http://www.databasejournal.com/features/mssql/article.php/3085211

I believe it will allow you to treat tables in different databases as if they were all in one database.

I haven't used the feature before, so I won't be able to provide you with much, if any, assistance with this. However, I have used MS Access Linked tables, which seem to be the MS Access version of linked servers. Perhaps someone else on daniweb can give you more information. Of course, there's nothing like just trying it stuff out and see the results; just make sure you use test databases and not your important data!