A wee bit of database design advice

[Dom]

tl;dr version: We've got an access control database that stores some user details and access rights to various web apps. We need a brand new fault reporting system which will store users, hardware, and fault details. My manager wants to mash the new fault reporting data into the existing access control database. Is it better to have two separate databases for two completely separate functions/systems, even though there'd be a (slight) overlap of information?


Full blown essay:

Hi all,

I've got a question about the design of a database. Well, I need to know what the "best practice" is in my situation, and why that's the case really.

Anyway, currently we have a database that contains some basic user information (login details an such) and information on our websites and restricted webpages. The database was designed, and is solely used, for maintaining access control over our websites. It basically determines who can access our various websites and apps.

Although the database contains some user information, it isn't used as an up-to-date definitive list of users, because some of our employees don't actually need to login to anything web-related and so won't be listed in the database; and we also have external people (not employed by us) that do need access to our websites, and so they are listed, even though they don't technically belong to our company.

I've been tasked with replacing a separate system - our fault reporting system - and it too includes storing information about our users, plus the various bits of hardware we have (PCs, monitors, etc) as well as details of all the fault calls that we receive.

When designing the database for the fault reporting system, someone pointed out that we already had a database (our access control database) that contains user details, and that that would be a good place to start. The project leader then decided that we should adapt this database to include all of the things that the fault reporting system needs, including extra user details, hardware details, and all of the fault call details.

So what we'll end up with is a database that will not only store access rights like it does at the moment, but will include all of the user, hardware, and fault information as well.

My gut feeling about this situation is that we're doing it all wrong. In my opinion, the Access Control database should be left as is, because its sole purpose is to control who can access what. I want to build a separate database for the fault reporting system; one that will actually be used to store up-to-date details for all of our users, hardware, and faults.

The advantages of adapting our Access Control database to include the fault reporting data is that we already have a table that has got most of our user details in it. We'll need to add some extra fields to it, but it's there. This, in my manager's opinion, will probably save time, and as always, saving time and/or money is the number one priority. It also means we'll only have one place that stores user info, rather than having two places, which is what we'd have if we made a separate database.

The cons, as far as I can see, are that we have an existing web interface to alter the data in the Access Control database, and it'll take time to see if altering the table structures (and adding new tables) will affect the web apps in any way. Another disadvantage is that I don't know if the data we wish to add to the database will affect the current relationships of the existing tables. If it does, that'll affect the web apps we have as well.

I know that having the same information stored in two places isn't a great idea, but there's nothing to stop us from building a web app that, when it alters data on the fault reporting system, it updates the necessary data on the access control database as well. We've already decided to build a new UI for the fault reporting system, so the work can be done.

I basically need a list of reasons why we should adapt an existing database to store fault reporting details, and a list of reasons why we should build a new database to store the fault details. From that we can decide which way to go.

Thanks!

[suedenem]

Why would adding new tables cause any problems elsewhere (assuming you aren't running your server at near-full capacity)?

Creating a new DB (with connection details, access authentication etc) sounds like a load of pissing about for no gain.

EDIT...

OK, I've read your essay a bit more closely.

I still can't see the point in duplicating user account details across different DBs, if they largely share records.  You should be able to add new fields or linking tables to add attributes and flags as appropriate without impacting upon anything else.

The advantages of doing this would include the ability to edit/disable users accounts in a single place, single backup point, not having to engineer a new DB structure from scratch and so on.

My only question would be whether there would be merit in using LDAP or similar to authenticate against domain users, then you really would have just a single point of administration...

[Dom]

Adding tables probably wouldn't cause any problems elsewhere, but adding the new fields/tables might require a slight reshuffling of where the current fields go, for normalisation purposes. The database isn't of the best design as it is, and I feel as though I'd be hacking together something new, rather than taking the time to design something properly, and that was designed to do the jobs we need it to from the ground up. Also, if we did need to slightly redesign the table structures of the existing tables to normalise them, it would involve adapting some of our existing web apps that already use those database tables, which would be a royal pain in the bum.

I see what you mean about having two places to edit users, but at the moment, what we've got can't really be considered a 'users' table. It simply contains usernames and passwords that can be used to access websites/webpages. They are assigned to users in most cases, but not all. As I mentioned before, we have some users that don't require a username to login to anything (and so aren't in the database table at all), and we have some external people who don't work for us (and so aren't one of users) but who do require a username and password to login to some of our sites.

It's all a bit woolly to be honest, and in an ideal world we'd have designed the whole thing to be both a user/hardware/fault call system, and an access control system. But as usual in business, things seem to grow organically with a mind of their own.

I think we'll build the fault call system separately, but keep our current UI for doing the access control admin work. We'll build a new UI for the new fault call system, and make sure that it maintains both the users/hardware/fault calls, and the access control. But keeping the existing access control UI as well will mean that we can create login details for users that don't actually exist should we need to, such as when we need one login and password for multiple users, or when we have external people coming in and need to access our sites.