It violates some principles of good database design. In the category table.... You cannot create a category without a user. This is known as an insertion anomaly. If more than one user is located in more than one category, then you have data replication, which could allow for data entry problems and is not space efficient. Would you rather store the word 'Category" 100 times or 1 time and use it 100 times?
At 02:25 PM 01/30/2002 -0500, you wrote: >---------- Original Message ---------------------------------- >from: "Douglas Brown" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >date: Wed, 30 Jan 2002 11:43:41 -0800 > >That looks like it would work fine. I prefer to use two tables instead >of 3 myself. less joining. > >[user] >id[PK] >name >address > >[category] >id PK >user_id (FK_USER.ID) >cat_name > > > > >There are two major products that come out of Berkeley: LSD and [Unix] >BSD. We don't believe this to be a coincidence. > > > >Doug Brown >----- Original Message ----- >From: "Jeffry Houser" <[EMAIL PROTECTED]> >To: "CF-Talk" <[EMAIL PROTECTED]> >Sent: Wednesday, January 30, 2002 10:34 AM >Subject: Re: (OT) Database Design Question > > > > This is how I would handle it. It sounds like a standard >many-to-many > > relationship. > > > > At 10:17 AM 01/30/2002 -0800, you wrote: > > >I'm putting together a new database for a fairly substantial >application I > > >need to build. > > > > > >Here is the kind of information I need to store: > > > > > >-- Registration of individual (standard name, address, email type of >stuff) > > >-- Categories of interest > > > > > >The categories of interest will come from a predefined table of about >100 > > >choices. The user will be able to make multiple selections. > > > > > >Here is my tentative plan on how to store this information > > > > > >UserTable (UserID, name, address, etc.) > > >CategoryTable (categoryID, categoryName) > > >CategoryMatchTable (CategoryID, UserID) > > > > > >This last table would store one row each for each categoryID selected >by > > the > > >User matching it with each UserID. > > > > > >I've used this method on similar problems before, but I thought I >would > > post > > >the question here and see if any of you great minds know of a more >elegant > > >way to do this (match users with multiple selections from a list >generated > > >out of another table). > > > > > >H. > > > > > > > ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
