Re: FW: db design - which is better

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hmmm...

What of maintenance?
One day your pointed-headed boss asks you to add a "civility" field
(Mr|Mrs|Colonel|...), a birthdate, etc.
How do you do that with a 1-table design? You will have to create 1-NF
temporary tables in the process...

How do you search for an author? You will have to create a needlessly large
and inefficient index on (LastName + FirstName), instead of a nice integer
index.

And when querying the articleID, I do not think that there should be a
significant performance penalty, if your indexes are properly designed.

cheers

Ignatius
_________________________
----- Original Message -----
From: "mayo" <mayo@xxxxxxxxxxxxxxxxxx>
To: "php-db" <php-db@xxxxxxxxxxxxx>
Sent: Sunday, February 08, 2004 00:10
Subject: RE:  FW: db design - which is better


> Let's say you have a page that is hit fairly frequently and spits out 100s
> of results.
>
> On it you have articleTitle, authorFName, authorLName.
>
> I ran a few tests and found that if all the above fields are in one table
it
> gets processed quicker than if there's a join. (authorID -> authorFName,
> authorLName)
>
> So, I'm beginning to think that processing time would be quicker if I
insert
> authorFName and authorLName into 2 tables. One the author table and the
> other the *VERY* frequently hit article table.
>
> That way when someone wants a list of articles, author FName, author LName
> all the data comes from one table.
>
> This would be probably very stupid in a financial or medical database
which
> has tens of millions of records and 100s if not 1000s of tables but SEEMS
> (I'm very open to being corrected) to work better on a fairly small web
> accessed database.
>
> yours,
>
> Gil
>
>
>
>
>
>
>
>
>
>  > -----Original Message-----
>  > From: Ignatius Reilly [mailto:ignatius.reilly@xxxxxxx]
>  > Sent: Saturday, February 07, 2004 6:01 PM
>  > To: mayo; php-db
>  > Subject: Re:  FW: db design - which is better
>  >
>  >
>  > There are perhaps such times, but I have yet to meet one.
>  >
>  > with 1NF:
>  > - your table space will be smaller
>  > - your indexes will work better
>  > - your SQL will be easier to write
>  > - ease of maintenance
>  >
>  > Just my 2 Belgian francs
>  >
>  > Ignatius
>  > _________________________
>  > ----- Original Message -----
>  > From: "mayo" <mayo@xxxxxxxxxxxxxxxxxx>
>  > To: "php-db" <php-db@xxxxxxxxxxxxx>
>  > Sent: Saturday, February 07, 2004 23:45
>  > Subject: RE:  FW: db design - which is better
>  >
>  >
>  > >
>  > > Thanks,
>  > >
>  > > There just seems to be times when non-normalized table structures are
>  > easier
>  > > to work with. A few hundred to a few thousand records, maybe 20
fields,
>  > > rarely adding another field.
>  > >
>  > > I wonder sometimes whether it make sense to put everything in one
table
>  > > instead of using joins.
>  > >
>  > > yours, putting-his-foot-in-his-mouth,
>  > >
>  > > Gil
>  > >
>  > >
>  > >  > -----Original Message-----
>  > >  > From: Ignatius Reilly [mailto:ignatius.reilly@xxxxxxx]
>  > >  > Sent: Saturday, February 07, 2004 5:33 PM
>  > >  > To: mayo; php-db
>  > >  > Subject: Re:  FW: db design - which is better
>  > >  >
>  > >  >
>  > >  > Use the second design. SQL 101 / 1st normal form.
>  > >  > _________________________
>  > >  > ----- Original Message -----
>  > >  > From: "mayo" <mayo@xxxxxxxxxxxxxxxxxx>
>  > >  > To: "php-db" <php-db@xxxxxxxxxxxxx>
>  > >  > Sent: Saturday, February 07, 2004 23:19
>  > >  > Subject:  FW: db design - which is better
>  > >  >
>  > >  >
>  > >  > >
>  > >  > >
>  > >  > > -----Original Message-----
>  > >  > > From: Gilbert Midonnet [mailto:glm@xxxxxxxxxxxxxx]
>  > >  > > Sent: Saturday, February 07, 2004 5:09 PM
>  > >  > > To: php-db
>  > >  > > Subject: db design - which is better
>  > >  > >
>  > >  > >
>  > >  > > I have a client who has hundreds of articles. Each article can
>  > >  > be seen by
>  > >  > > one or more permission groups.
>  > >  > >
>  > >  > > I have questions regarding setting up the an
>  > article_display table.
>  > >  > >
>  > >  > > The easiest table to read and to conceptualize would list the
>  > >  > articles and
>  > >  > > use a boolean for each of the permission groups (let's
>  > call the PGs
>  > for
>  > >  > this
>  > >  > > example).
>  > >  > >
>  > >  > > (articleNameID refers back to the article table)
>  > >  > >
>  > >  > > articleNameID---PG1---PG2---PG3---PG4---PG5
>  > >  > > 1001-------------1-----0-----0-----0-----0
>  > >  > > 1002-------------1-----1-----0-----0-----0
>  > >  > > 1003-------------0-----0-----1-----0-----0
>  > >  > >
>  > >  > >
>  > >  > > Or should the table set up be:
>  > >  > >
>  > >  > > articleNameID--PG
>  > >  > > 1001------------1
>  > >  > > 1002------------1
>  > >  > > 1002------------2
>  > >  > > 1003------------3
>  > >  > >
>  > >  > > etc...
>  > >  > >
>  > >  > > gil
>  > >  > >
>  > >  > > --
>  > >  > > PHP Database Mailing List (http://www.php.net/)
>  > >  > > To unsubscribe, visit: http://www.php.net/unsub.php
>  > >  > >
>  > >  > >
>  > >  >
>  > >  > --
>  > >  > PHP Database Mailing List (http://www.php.net/)
>  > >  > To unsubscribe, visit: http://www.php.net/unsub.php
>  > >  >
>  > >
>  > > --
>  > > PHP Database Mailing List (http://www.php.net/)
>  > > To unsubscribe, visit: http://www.php.net/unsub.php
>  > >
>  > >
>  >
>  > --
>  > PHP Database Mailing List (http://www.php.net/)
>  > To unsubscribe, visit: http://www.php.net/unsub.php
>  >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux