Re: proper db standard

On 6 Dec 2002 at 10:45, Jodi Kanter wrote:

> I am creating a simple database that will hold information about
> various pu= blications. There are keywords that are associated with
> these publications = and there can be anywhere from 1 to about 6 of
> these different keywords.
> 
> As I see it I have two choices:
> 
> 1) create keyword fields 1-6 in the publications database and accept
> that s= ome of these fields will be empty. 

That is unnormalized data and will make queries more awkward.


> 2) create two tables:
> "publication" and "keyword". In this scenario I have = no limit on the
> amount of keywords that are used 

You can control the number of keywords in the application and via 
triggers in the database.

> and I don't have empty fie= lds.

What is the signifiance of empty fields?  You can always determine 
the number of keywords for a given publication with this:

select count(*)
from keywords, pubication
where keywords.publication_id = publication.id;

> However, many of the keywords repeat for different publications. In
> th= is situation I would have some repeating words in the columns.

There's nothing wrong with that

> I lean toward #2 but wanted to see if there was a preferred standard
> or ano= ther possibility that I am overlooking??

I would recommend #2.
-- 
Dan Langille : http://www.langille.org/