Best Practise

[knobby2k]

Hey guys,

 

I'm relatively new to PHP / MySqL so i'll be posting quite a few questions which i'm sure you lot will be able to help with. I know the basics and have done a work related site in this before but I now want to do a site as a project for work and want to get things spot on.

 

Background of the project:-

 

Site to allow a user to register, upload a document and tag the document with relevant tags to the content, which would allow other users to search for relevant material using these tags.

 

I'm looking for a bit advice with best practise firstly so...

 

1) for security, would you have a seperate DB's, 1 for the users and 1 for the other stuff OR would you have 1 database but seperate tables?

 

2) would you include admin users in the normal users table and just give them a field called user_type to differentiate the type of user (e.g. user_type = admin, user_type = standard, etc...), or would you again seperate this on to a seperate DB rather than seperate table.

 

Your help is much appreciated!!

 

Cheers

 

[ignace]

Answers differ depending on your background. Your first concern should be performance. No matter how small/big your project, people won't tolerate a SLOW website. Often your website is SLOW due to bad or non-existent index definitions. Learn how to normalize your DB, and how you define proper indexes. Doing so, will save you lots of money down the road. Hardware-driven optimization is costly.

 

1) Keep it all in one DB, but keep in mind when writing your code that some tables may be switched to a different DB

2) Using an extra column to store the user_type is a well-known technique. The user_types themselves are best kept in a separate table and referenced using a foreign key to the users table.

[mikosiko]

+1 with Ignace answers, but also considering your project description

"Site to allow a user to register, upload a document and tag the document with relevant tags to the content, which would allow other users to search for relevant material using these tags"

maybe you should consider to use Drupal or Joomla

[knobby2k]

Hey,

 

Thanks both for your answers it really is appreciated.

 

I'd like to make this from scratch as an interest rather than use droopal.

 

So you would suggest something along the lines...

 

1 DB

1 User Table

User_Type to differentiate the user type but using a foreign key as the identifier.

 

So in code terms (roughly)...

 

[u][b]User_type_table:-[/b][/u][table]
[tr]
[td][b]type_id[/b][/td][td][b]user_type[/b][/td]
[/tr]
[tr]
[td]1[/td][td]admin[/td]
[/tr]
[tr]
[td]2[/td][td]standard[/td]
[/tr]
[tr]
[td]3[/td][td]guest[/td]
[/tr]
[/table]

[u][b]User_table:-[/b][/u]
[table]
[tr]
[td][b]user_id[/b][/td][td][b]user[/b][/td][td][b]user_type[/b][/td]
[/tr]
[tr]
[td]1[/td][td]John[/td][td]1[/td]
[/tr]
[tr]
[td]2[/td][td]Mark[/td][td]1[/td]
[/tr]
[tr]
[td]3[/td][td]Iain[/td][td]2[/td]
[/tr]
[tr]
[td]4[/td][td]Bob[/td][td]3[/td]
[/tr]
[/table]

 

Is that right in my way of thinking?

 

So this should be the quickest method of indexing and performance for the site?

 

Cheers for your help again, it really is a big help to be able to ask experts!

 

 

[ignace]

So this should be the quickest method of indexing and performance for the site?

 

You have done no indexing, you just showed us your table schema. But yes, the schema is correct. Although you shouldn't mention that it's a table (User_type_table), it's obvious.

[knobby2k]

yeah just an example, thanks that makes a lot of sense now, it helps me get my head around it!

 

I'll mark this as solved (when i find the solved button lol)

 

Cheers