How do i structure my SQL Database (tables, Schemas, users, stored procedures etc.) to prepare it for an n-tier architecture?

I think the title is self explanatory.

What I'm looking for is material so I can further my knowledge. I've never developed a full application before so building one from scratch is a bit overwhelming for me. And the first bump in the road is the database.

Websites, 开发者_如何学运维articles, books, elaborate answers, anything will do as long as they keep me on the right track.

Thanks

UPDATE: Sorry for not mentioning earlier. The platform is .NET and Winforms.

Well, best answer is - not at all. Dont et me wrong, but..... the sql structure is not really dependant on how the application is structured.

Some ideas, though: + If you want a business layer, stay light on stored procedures - your logic is supposed to run in the business layer. SOME sp#s make sense, though - sometimes it is better for performance.

I would suggest Scott Ambler's "Building Object Applications That Work" - ancient but good.

The one mistake I did in the beginning of my programming days, is not use a database class there are many out there, i have one that i have added to over the years, but this will speed up your development time. As far as database goes there are so many rules and things you learn with trial and error... the internet is a great resource. SQL is pretty easy to pick up.

I dont know what your level is so ill go over some basics that i know of...

I would say a few do's and don'ts Dont name your database, tables, & columns common names ie "users", "products" come up with your own naming structure something like

database : prod_dbb9 table : tbb_users column : u_uid, u_userid

This will prevent someone from guessing your tables or database structure and allow you to hack your db.

Sql injection is pretty easy to do... so always escape your sql commands to the database! PHP has a built in function for that mysql_real_escape_string

always remember ID numbers are easier on the database than text so if you can pull records by id numbers

ie: profile.php?id=123 rather than profile.php?username=jason

Kind of obvious but don't name your querystrings the same as your columns in your db ie

profile.php?u_userid=123

If you are deletin only one record make sure to put "limit 1" at the end of your SQL this will help prevent more records from getting deleted by hackers.

Always encrypt your users passwords. You can choose to use php MD5() but be aware that this is one way encryption...(no one can ever see this password again) so if your user forgets their password you will have to generate them a new password and mail it to them...

Well thats all i got for now.. good luck!

• Jason