Mango



Thread: Database Comments

Created on: 10/18/06 01:32 PM

New topic Reply    Page: 1  

Replies: 3
Thomas


Thomas's Gravatar
Joined: 10/18/06
Posts: 2

10/18/06 1:32 PM

Laura,
I guess I come at this more from the DB side of the house then from CF since I have been programming SQL Server 2000 and databases longer than I have been programming CF. I usually go through an app like this by first looking at the DB structure to get a feel for the underlying setup for an app since I consider myself more of a DBA than a "programmer" per se and where it can be improved. Something that just absolutely baffles me is why folks in the CF community have chosen to use something like:
id varchar(35) NOT '
as their PK for a table versus actually using something like an int, bigint, etc for the database. What is being done is "blasphemy" in a DBA's eyes when you set up a database because of a few reasons:
- databases like integers much better than alphanumerics aka varchar
- SQL Server can sort integer data much faster than character data
- smaller datatype and smaller memory footprint on the DB server
I have heard all the "reasons" for why someone would use it from Raymond Camden, but I still don't buy it because it is inefficient. Besides, it makes for an obnoxiously long URL which can be rather painful to type in or memorize.

So I will probably end up changing the code around in the MSSQL setup so it is more MSSQL friendly. I am not picking at the code, I am merely looking at the application from the database since that is where all the information will be stored.

Here's a link for you to read if you want the pro's and cons of it all.
http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

To me it is just good common-sense use an identity field. Anyway, as I go through playing with and setting up this I will be posting my observations here.

Also, out of curiosity, were you going to look at setting up the blog so it can be run in a "multiple blogger" format?
Oh yeah, thanks for inviting me to look at it!
Con mucho respeto,
Gracias,
Tom
Link | Top | Bottom
Laura

Wizard
Laura's Gravatar
Joined: 01/29/05
Posts: 1534

10/18/06 2:33 PM

Tom,
I understand your point. It was a very hard decision that I had to make. In my applications, I usually use int, but also my applications are targeted to one particular database vendor. I use the factory pattern anyway just in case, but I only have to code for one db type.
When I created Mango, it was important to make it very portable. I used to use int and I use the factory pattern here too so that dao are interchangeable and there is a setting for the db type so that if a different sql is needed for a certain database, it can be easily incorporated without using <cfif> or switch statements. But, I made a turn to uuid when I started using inheritance for posts/pages and similar code started to get duplicated for both mssql and mysql databases. At that point I decided that I didn't want to deal with getting the inserted id and make sql statements as simple as possible.
For that same reason, I didn't use views.
Now regarding searching for ids... In no url the ids appear (only in the admin). I use aliases extensively to make urls more friendly. For that reason, alias columns are indexed and are what I usually search by (for example when showing a post). It is true that the foreign keys refer to the ids and joins might get slower. But again, there are trade offs...

I also had to decide on naming conventions... and I am libra which means that I find it difficult to make decisions ;)

Thanks for your input.

What exactly do you mean by multiple blogger?
Link | Top | Bottom
Thomas


Thomas's Gravatar
Joined: 10/18/06
Posts: 2

10/18/06 9:50 PM

Laura,
No problem, I was curious as to why it was done that way. Thanks. I meant having different bloggers all being able to use the same blog but separate logins without having to install the files into a separate directory. Something I think I read on a separate post here. Anyway, I'm aquarius and I guess I like difficult things. Anyway, I like it very well so far!
Tom
Link | Top | Bottom
Laura

Wizard
Laura's Gravatar
Joined: 01/29/05
Posts: 1534

10/20/06 4:44 PM

Tom,
You want different people writing for the same blog?
If so, then it is just a matter or adding "Authors" from the admin...
Link | Top | Bottom

New Post

Please login to post a response.