I am about to create a php/mySQL web app, where each user will have its password protected account where they can create/store their records/data. I am pondering how to approach the database architecture side of things. Should I create separate mySQL table for each user’s data? In the past I have created similar site, but all users records where kept in massive single table.
Any advice how to handle this and best practices is appreciated.
Basically, each new user (for ex, hairdresser) will create account and keep track of their appointments. User’s data fields will have such info as clients name, address, date of appointment, etc.
So I will create one main table (tblUser) which will contain account holders credentials, and create a separate user data table (tblUserData) with a foreign key fldUserId for each new user linked to the user account table’s (tblUser) primary key fldUserId.
What if existing user wants to add their associates to their account who should have access to primary user’s data (appointments)? Should I add them to 'user account table (tblUser) with a new User ID? Should I create separate ‘company account table’ (tblCompany)? 'User account table (tblUser) will have a a foreign key fldCompanyId linked to the 'company account table’s (tblCompany) primary key fldCompanyId.
With the questions you are asking I think you are putting the cart before the horse.
Before you start designing your database model, you need to establish the “Business Rules” for your application. Once you have your business rules defined (which specify how your application is to work) then you can start designing your database model and build your database.
For example, the business rules should include answers to -
What data makes up user data
Can a user account have more than 1 user data record
What data makes up user’s associate data
Can a user account have more than 1 associate.
What is the relationship between associate data and user data
The more business rules you can provide the easier it will be for someone to help you design a data model.
A simplistic data model, depending on the answers to the above, could be something similar to:
I’ve just read this thread and, while my query is * slightly * off-topic, I was just wondering if I could just interject and ask you, Kalon, what a Foreign Key is, and how one can link to / use this, to make a database relational?
There’s something called EAV model (Entity-Attribute-Value).
Such approach to database modeling is extremely useful when you have no idea what fields you’ll need, whether certain users (or groups) will have more fields than the other and the list goes on.
There are drawbacks to the method also, which I won’t try to explain so I’ll just link up an article describing the usage of EAV in a rather popular PHP application.
Have at it: http://techportal.ibuildings.com/2010/10/21/the-eav-data-model/
@Kalon - there’s nothing wrong using terrible field/table names. What’s wrong is using those in examples you give to others. That way we get more people using horrible naming conventions, thus we get higher heart attack rate once we need to edit and amend that terrible application that makes our lives miserable.
No offense of course.
Thanks for the reply. I think I termed my question slightly incorrectly. I know / get the gist of what a ‘foreign key’ is/does, but what I’m after is how I could use this to achieve what I want to do, how I implement it, and how my code might change as a result.