flash gordon Posted November 11, 2009 Share Posted November 11, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/ Share on other sites More sharing options...
flash gordon Posted November 13, 2009 Author Share Posted November 13, 2009 bump Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-956531 Share on other sites More sharing options...
Alex Posted November 13, 2009 Share Posted November 13, 2009 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 Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-956532 Share on other sites More sharing options...
flash gordon Posted November 13, 2009 Author Share Posted November 13, 2009 I was talking in terms of databases and not tables. Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-956655 Share on other sites More sharing options...
Highlander Posted November 15, 2009 Share Posted November 15, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-957709 Share on other sites More sharing options...
keldorn Posted November 22, 2009 Share Posted November 22, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-963530 Share on other sites More sharing options...
flash gordon Posted November 23, 2009 Author Share Posted November 23, 2009 oh dear goodness.....close the thread. sigh. Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-963847 Share on other sites More sharing options...
zeodragonzord Posted November 23, 2009 Share Posted November 23, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-963999 Share on other sites More sharing options...
plznty Posted November 23, 2009 Share Posted November 23, 2009 using multiple databases is not logical, when they tables could simply be shared. Solve this topic. Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-964018 Share on other sites More sharing options...
flash gordon Posted December 10, 2009 Author Share Posted December 10, 2009 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. Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-975084 Share on other sites More sharing options...
Daniel0 Posted December 10, 2009 Share Posted December 10, 2009 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... Quote Link to comment https://forums.phpfreaks.com/topic/181084-database-design-and-segregation-of-reponsiblilites/#findComment-975091 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.