Re: FN-FORUM: [OT] flippancy - What's the best development platform for creating Web 2.0 business applications?

date posted 22nd November 2007 13:22

Hello Graham,

And thanks for giving up your lunch break :-)

On 22 Nov 2007 13:47:35 -0000, Graham Stark wrote
> I think there
> are three things to worry about about the first version:
>
> 1) If you *just* have ids as the primary keys, then you can't
> (legally) use foreign keys to specify how tables relate to one
> another.

Yes you can. Here's an example in MSSQL where the child table is related to
the parent table.

CREATE TABLE Parent
(
ParentId
INT
NOT NULL
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
Tag
VARCHAR(20)
NOT NULL
)
go

CREATE TABLE Child
(
ChildId
INT
NOT NULL
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
ParentId
INT
NOT NULL
REFERENCES Parent(ParentId),
Tag
VARCHAR(20)
NOT NULL
)

> 2) If there are things that really make something unique - for
> example, that no two users can have the same nickname, then in the
> first case you have to express that somewhere else, either in
> application code or in some otherwise redundant index;

Or by using a unique constraint which as its name suggests, is designed for
that very purpose.

> 3) It's often much easier to query the second version. An example: a
> typical task might be to display all messages posted by 'john' about
> 'football'. Messages in the 'normalised/natural key' version might look
> like:
>
> user_name | topic_name | message_id | body
> 'John' 'Football' 3 stuff.....
>
> So the query is just:
> select body from message where user_name = 'john' and 'topic_name' =
> 'football';
>
> Whereas, in the unique id version, a row might look like:
>
> user_id | topic_id | message_id | body
> 3 22 2 Some stuff.....
>
> So we don't know which user_id refers to 'John' and which topic_id is
> 'football'. You have either to look them up in the query by joining all
> three tables:

Yes you have to do a join, but this is what RDBMSs do and its less of a
handicap than your method where there can only be one user called 'John'! If
there is a second user called 'John' what do you do, call him 'John2', and
is the next called 'John3'? I'm seeing an auto int here am I not?

I don't want to sound "holier than thou" twice in one day, and I'll say this
as gently as I can and hope that I don't cause offence, but I do believe
that you have not quite grasped Normal Form.

--
Gary Short
http://www.garyshort.org