Seeking advice on MySQL backend architecture

PHP
1

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.

2

it would help if you specified what the users’ data was made up of and what columns it will need.

But everything else being equal, you should be able to create a separate user data table (tblUserData) with a foreign key fldUserId linked to the user account table’s (tblUser) primary key fldUserId.

3

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.

4

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 -

  1. What data makes up user data

  2. Can a user account have more than 1 user data record

  3. What data makes up user’s associate data

  4. Can a user account have more than 1 associate.

  5. What is the relationship between associate data and user data

etc
etc
etc

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:

tblUser

fldUserId - PK
fldFname
fldLname

tblUserData

fldUserDataId
fldUserId - FK
fldClientName

tblAssociates

fldAssocId - PK
fldUserId - FK
fldCompanyName

5

dear database developers

please remove “tbl” from your table names and remove “fld” from your column names

i mean, really

thank you, that is all

:cool:

6

why?

I don’t see any harm in using Hungarian Notation or a variation of it.

It’s basically a persoanl choice whether developers use them or not.

I also use in html id’s by prefixing textboxes with txt, select lists with sel, radio buttons with rad, checkboxes with chk etc etc

For me, it makes javascript/php/SQL code easier to read - but that’s just me :slight_smile:

7

yeah, it’s just you :smiley: :smiley:

is “nounParis prepIn artThe nounSpring” easier to read?

search that phrase, Joe Celko doesn’t think so either

see also http://www.sitepoint.com/forums/showthread.php?t=539264

8

than what? :confused2:

You, and probably many others, obviously don’t feel prefixing tables and fields has any benefits and I have no problem with that.

I believe they do and so I will continue to use them as I see fit.

But I am not going to :argue: with anyone on why I use prefixes because as I said earlier it is totally a personal choice.

If you are genuinely concerned about me or anyone else using prefixes then consider starting a new thread instead of attempting to hijack the OP’s thread because this is clearly, imho, off topic.

9
Off Topic:

than “Paris In The Spring” you numpty

but i mean that in the nicest way possible

:slight_smile: :slight_smile:

we already gots a few

so please keep your shirt on

but you are totally right about the hijack, and i apologize

just wait until i catch you doing it :smiley: :smiley:

10

but there is nothing you can do about it :smiley:

the worst possible scenario for me, if using prefixes is breaking any sitepoint rules, is that I will eventually get banned.

but then I can still use prefixes as I like, regardless of what you think, and post in any other forums I like, as I do now, that let me :slight_smile:

cheers :wink:

11

[ot]

i meant just wait until i catch you hijacking a thread

you can use all the prefixes you want, i don’t really care how difficult you make your own life :smiley: :D[/ot]

12

ok thanks :slight_smile:

“difficult” is a relative term.

what’s easy for me might be difficult for someone else - and vicky vercky :slight_smile:

My life is very easy atm.

see you around :wavey:

13

@ the OP

it’s safe to come back now if you would like more help :slight_smile:

14

Hey Kalon, OP, etc :slight_smile:

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?

I suspect this is a really ‘duh’ question, for which apologies, but I’m trying to do something (see thread: http://www.sitepoint.com/forums/showthread.php?t=712186) and I’m wondering whether this “foreign key” / “primary key” thing might help me out too!

Thanks in advance, and sorry for butting-in. :slight_smile:

Alex

15

I’d rather walk into an existing project with hungarian notation than nothing at all. What a nightmare.

16

i’m not Kalon, but since this was addressed to “etc.” i would like to respond

“what’s a foreign key?” is answered by almost every basic SQL tutorial

please go have a google and let me know if you have any further questions

17

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.

18

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. :slight_smile:

Thanks,

Alex

19

foreign keys aren’t necessary, just add a “featured” column to your properties table

20

Kalon and others,

Thank you for your feedback. I think I have enough to get started.