943,712 Members | Top Members by Rank

Web Development > Databases > Database Design > Database Design Help
Ad:
Permalink
Sep 20th, 2008
0

Database Design Help

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ashamsa is offline Offline
4 posts
since Sep 2008
Permalink
Sep 22nd, 2008
0

Re: Database Design Help

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.
Reputation Points: 27
Solved Threads: 29
Posting Whiz
timothybard is offline Offline
317 posts
since Mar 2007
Permalink
Sep 23rd, 2008
0

Re: Database Design Help

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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ashamsa is offline Offline
4 posts
since Sep 2008
Permalink
Sep 23rd, 2008
0

Re: Database Design Help

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?
Reputation Points: 27
Solved Threads: 29
Posting Whiz
timothybard is offline Offline
317 posts
since Mar 2007
Permalink
Sep 24th, 2008
0

Re: Database Design Help

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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ashamsa is offline Offline
4 posts
since Sep 2008
Permalink
Sep 24th, 2008
0

Re: Database Design Help

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.
Last edited by timothybard; Sep 24th, 2008 at 10:24 am. Reason: Added last paragraph.
Reputation Points: 27
Solved Threads: 29
Posting Whiz
timothybard is offline Offline
317 posts
since Mar 2007
Permalink
Sep 26th, 2008
0

Re: Database Design Help

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..
Reputation Points: 10
Solved Threads: 0
Newbie Poster
ashamsa is offline Offline
4 posts
since Sep 2008
Permalink
Sep 26th, 2008
0

Re: Database Design Help

Here is a link to an article about Linked servers in MS SQL Server. http://www.databasejournal.com/featu...le.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!
Last edited by timothybard; Sep 26th, 2008 at 9:18 am. Reason: Added Link
Reputation Points: 27
Solved Threads: 29
Posting Whiz
timothybard is offline Offline
317 posts
since Mar 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Database Design Forum Timeline: Need a opinion/advice about university students enrollement system db pls HELP
Next Thread in Database Design Forum Timeline: Division Operation in realtion algebra





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC