Database Design and segregation of reponsiblilites

[flash gordon]

I'll give my scenario then ask a couple of questions.

 

I have system which requires users to login (authentication). Once logged in, a user can manage a project by creating, modifying, assigning, attaching files or notes, etc to project. The authentication part could also used by another system such as billing. I have 2 (or more) application that can all benefit from 1 authentication system.

 

How do I split up the databases? Do I have database for authentication which would have tables users, groups, etc. And then another database for just the "System A" which would have tables like projects, specs, etc. And then another for billing? Or should all of these fall under 1 databases because they all use authentication?

 

Here's the thing I think is a bit weird. The applications and databases are nearly independent of each other. The only exception is my SystemA.notes table has references to "user ids" which would be in the Authentication.users table. The id would be a foreign key to not just a table but a database. Is this typical? What should be my structure?

 

thanks for the help.

cheers

[flash gordon]

bump

[Alex]

I would definitely go with the three separate tables. You don't want to be storing unrelated data together in the same table. Trying to manage all of this inside 1 table would be a very bad design flaw that would just make things much harder for you in the long run.

 

I suggest reading up on database normalization

[flash gordon]

I was talking in terms of databases and not tables.

[Highlander]

I don't see any problem with just having one database and multiple tables, and then give different applications different type of access to the data (using views or stored procedures).

 

There is no need for using multiple databases when you have connections to different tables.

 

[keldorn]

I would definitely go with the three separate tables. You don't want to be storing unrelated data together in the same table. Trying to manage all of this inside 1 table would be a very bad design flaw that would just make things much harder for you in the long run.

 

I suggest reading up on database normalization

 

Just read that wikipedia article, its makes sense. I think its no brainer.

Say you make a forum software and you save the names of the posters in  the threads with a hardcoded name, not the ID. So if you want to change your name , it has to modify every single row in the table where your name might appear. It would of been better to save your ID in the row, and match your Id to your name in member table with recursion.

Otherwise if you didn't then if you change your name, then previous posts will still have your old name. Thus now you have a Database anomaly. 

 

I think Vbulletin made this mistake early on in their design. On some forums they won't allow you to change your name because of the amount of Sql queries it might need to do it.  I'm only assuming this by reading this. But he may be full of shit, or maby not, if rigth, then yeah, Vbulletin made a mistake in database design.

 

But the other way it would be simple, you would just have to update one row in the member table of their name and your done. The rest is simple recursion.

[flash gordon]

oh dear goodness.....close the thread.

 

sigh.

[zeodragonzord]

Unless you see a huge growth in the number of tables it would be best to keep it all in one database.  This will allow you to use the same database connector in your programs, saving processing time and less objects to keep track of.

 

If you decide to use two databases, you could still use one database connector in your code, but you will have to run 'USE databaseName' each time you want to switch databases.  You can imagine that after a while, it can become a pain to keep track which one your connector is using when you go back to code your programs.  You could also fully qualify your database table names in your queries if you join them across database, but if you're doing large queries, it will become hard to read.

 

"Select * FROM databaseName1.tableName1

INNER JOIN databaseName2.tableName2 ON databaseName1.tableName1.field1 = databaseName2.tableName2.field2

WHERE........"

 

Another advantage to keeping it all in one database is that backing up will be easier.  Again, you're backing up one database instead of two.  You're keeping the whole thing current, under the same version, at the same point in time.  If you kept it as separate databases, you might end up with problems when you need to restore one database from one point in time but left another database from another point in time, resulting in mixed problems that your code does not expect.

 

Are you pondering keeping the authentication data in a separate database to keep it organized?  If your main reason is organization, I would advise that you use a naming convention to keep track of related tables instead.

 

auth_contact

auth_organization

auth_session

application1_table1

application1_table2

application2_table1

...

 

However, if you're developing a large scale application such as an MMORPG, then it would be best to keep it in a separate database so you can have a dedicated server handle authentication only.

 

Hope this helps.

[plznty]

using multiple databases is not logical, when they tables could simply be shared.

Solve this topic.

[flash gordon]

Thanks zeodragonzord! Yeah, that helps a lot. The main reason I'm was asking is not organization but expandability. I have 6 applications (and that number continues to grow) that all use the same authentication code. It seems most expandable to have the authentication in it's own encapsulated place with each application in its own as well.

 

Thanks again, zeodragonzord.

[Daniel0]

You could also fully qualify your database table names in your queries if you join them across database, but if you're doing large queries, it will become hard to read.

 

"Select * FROM databaseName1.tableName1

INNER JOIN databaseName2.tableName2 ON databaseName1.tableName1.field1 = databaseName2.tableName2.field2

WHERE........"

 

That's what aliases are for...