Setting up a SQL Server database 1

[silverbeetle]

hi all.

i've worked with pre-existing SQL Server databases but have only ever created Access databases from scratch. i'm about to create a little personal site as a learning tool and i want to use SQL server this time around.

it all seems generally straight forward but i was wonder what 'not so obvious' issues i should consider in its creation (keeping in mind i want to learn the principles of a large scale website/database).

i guess the main issues that spring to mind is how security and users could be implemented into my CF project.

any info would be very much appreciated

thanks in advance, chris

 

[GunJack]

Hey Chris,

I don't really consider there to be much difference between using MS Access and SQL Server from a design perspective. You obviously have a lot more tools to work with when using SQL server (stored procs, triggers, built in functions) but the same design principles you use on one db would apply to the other. I think the biggest part of it is structuring your database logically so data is separated into tables where it needs to be and you have a good logical arrangement.

As far as security goes, you have more control with SQL server since you can create specific logins and assign rights to those users as to what they can do in your databases. You could create specific accounts and limit them to what they can do such as only selecting data on some tables, inserting on others, etc.. but I usually do all of my security/screening inside of CF so I never really need to use the SQL server security. I think it's more for controlling access to the db in a design environment where you have developers modifying the database. In your case, I'm guessing security is an issue with users of the system and not developers working on it. In this case, the security and user management is really part of your application and not tied to any specific db.

Designing a system to manage users and rights is no trivial application but in a lot of cases you can get by with the basics and not worry about a robust security model. If you have any specific questions, I'll be happy to give my opinion

Hope this helps,
GJ

 

[silverbeetle]

Thanks GunJack.

With the sql server user logins then, are there any instances where you do use them and not for the purpose of protecting database manipulation from other developers on your team? could you give a scenario?

I'm just curious to know when I should use the log ins as you were right that it's not really an issue with my current project.

Also, if i was to use the sql server user logins how does this effect my cf code? how does cf utilise the sql server features. any code snippit example would be great.

thanks!
chris

 

[GunJack]

Hey Chris,

I'm not an experience sql server admin but to the best of my knowledge, the only time the sql logins would be used by your cf code is when connecting to the database. You have to supply a username and password when setting up the sql server odbc connection so you really don't even need to use them in the code. You could however leave these off when setting up the odbc connection and then require the CF code to pass them in like this:

<cfquery name=&quot;q1&quot; username=&quot;test&quot; password=&quot;pass&quot;....>

If they are not specified in the odbc settings, the query will fail to run without them being passed in manually. Outside of this, I don't think your CF code will ever use them.

Hope this helps,
GJ