[mso] Re: Database Normalization
- From: Nikunj Shah <catchnikunj@xxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Tue, 10 Sep 2002 23:16:21 -0700 (PDT)
Thank you so much for help.
But shouldn?t Bound column property be set to 1
instead of 2. I have tried it, and it worked!!!!
Just for your information, I have received some other
wonderful suggestions as well. One of them is as
under.
" You need another table to link the topics to the
quotes, let's call it LINK. The primary key of this
table is composite (I think it is the correct english
word, not sure anyway), i.e. made of two fields : the
one that is the primary key of the Topics table and
the one that is the primary key of the QuoteList table
(to make a primary key with two fields, select them
while pressing the Shift key, then click on the
primary key icon). You can remove the topics fields
(1-2-3...) from the QuoteList table, they are now
useless (and not in normal form number 1, you'll be
punished !). You can have as many topics as you like
for a quote (and as many quotes as you like for a
topic). Queries become much easier, though using one
table more (i.e 3). The LINK table must be linked from
MANY to ONE towards each of the two other tables,
that's the important point. In your case, you don't
need other
fields in the LINK table, it only contains its primary
key (2 fields), but you can imagine adding later
something like "major" or "minor" topic for instance.
The author table is OK if you accepts that a quote has
only one author.
Good luck ! "
Thank you So much once again
Regards,
Nikunj
--- James LaBorde <jlaborde@xxxxxxxxx> wrote:
>
> Nikunj,
>
> Your database sounds like it is in relatively good
> shape. The only change I
> would make, and hopefully you don't have too much
> data in there already,
> would be to change your lookup fields. If you
> change them to lookup the
> TopicID, and display them as the topicname, you will
> only need to correct
> your entries once.
>
> Here is what you will do. Change your source to
> include the Topicid and the
> topicname, in that order. You may need to change
> your field type to long
> integer. Next, in the properties window, change
> your bound columns to 2 and
> for width, enter 0. This will cause Access to set
> the TopicId width to
> nothing and your topicname width to its default
> setting. You can adjust it
> from there.
>
> By doing this you save some space as you will only
> store the id rather than
> the entire text of the topic each time. You will
> also make life much easier
> on yourself as any changes to topicnames are
> automatically filtered down to
> the Quote table.
>
> If you already have data in your topics fields of
> your quote table, you will
> need to adjust it to the appropriate id before doing
> this. If you need
> help, feel free to ask.
>
> James La Borde
> South Western Federal Credit Union
>
> -----Original Message-----
> From: Nikunj Shah [mailto:catchnikunj@xxxxxxxxx]
> Sent: Tuesday, September 10, 2002 9:54 AM
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Database Normalization :VSMail MX1
>
>
>
> I have read a few pages on Database normalization,
> and
> understand that the same data should not be entered
> in
> a database in more than one table, and that each
> table
> should hold only related fields.
>
> Now consider this problem
>
> I have created a database to store quotes by great
> authors. I want each quote to be assigned a
> particular
> topic. However the problem is that, a particular
> quote
> may be such that it can be assigned two topics or
> may
> be even more. Consider for example the quote "It is
> the surmounting of difficulties that make heroes" by
> Louis Cossuth. This quote may be assigned topics
> like
> "Hero", "Difficult" and "Challenge".
> After entering a quote, my main utility is to be
> able
> to search for Quotes, Topic wise.
>
> Keeping this fact in mind my current database has
> the
> following Structure
>
> Table QuoteList
> Fields:
> QuoteId
> QuoteText
> Topic1 (Lookup To TopicName in TopicMaster Table)
> Topic2 (Lookup To TopicName in TopicMaster Table)
> Topic3 (Lookup To TopicName in TopicMaster Table)
> Topic4 (Lookup To TopicName in TopicMaster Table)
> AuthorName (Lookup to AuthorName in AuthorMaster
> Table)
>
>
> Table TopicMaster
> Fields:
> TopicID
> TopicName
>
> Table AuthorMaster
> Fields:
> AuthorID
> AuthorName
>
> I currently search for quotes on particular topic by
> using "Or" in the querry.
>
> Further a quote may be such that it can be assigned
> only one topic. Thus it happens that since some
> quotes
> can be assigned only one topic, other topic Fields
> viz
> Topic2, Topic3, Topic4 remain blank.
>
> In such design, I face all drawbacks of using a
> non-normalized database. Like for eg, if I misspell
> a
> topic once in TopicName field of TopicMaster Table,
> the incorrect spelling is entered in Quote Table
> since
> all topic fields in Quote table have a lookup in the
> TopicMaster table. Further when I correct the
> spelling
> in TopicMaster table I have to manually search all
> fields in Quote table where the incorrect spelling
> of
> the topic is entered and correct the same.
>
> I am sure that my Database is not normalized. It can
> be better designed such that redundancy can be
> avoided, as well as data assess and search
> capabilities are improved.
>
> Any Suggestions from Access Gurus for normalizing
> the
> database??
>
> Thank you so much for help
>
> Regards,
>
> Nikunj
> Mumbai
> India.
>
>
>
>
>
>
> __________________________________________________
> Yahoo! - We Remember
> 9-11: A tribute to the more than 3,000 lives lost
> http://dir.remember.yahoo.com/tribute
>
*************************************************************
> You are receiving this mail because you subscribed
> to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to
> mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to
> mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
>
> Or, visit the group's homepage and use the dropdown
> menu. This will also
> allow you to change your email settings to digest or
> vacation (no mail).
> //www.freelists.org/webpage/mso
>
> To be able to use the files section for sharing
> files with the group, send a
> request to mso-moderators@xxxxxxxxxxxxx and you will
> be sent an invitation
> with instructions. Once you are a member of the
> files group, you can go
> here to upload/download files:
> http://www.smartgroups.com/vault/msofiles
>
*************************************************************
>
*************************************************************
> You are receiving this mail because you subscribed
> to mso@xxxxxxxxxxxxx or
> MicrosoftOffice@xxxxxxxxxxxxxxxx
>
> To send mail to the group, simply address it to
> mso@xxxxxxxxxxxxx
>
> To Unsubscribe from this group, send an email to
> mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
>
> Or, visit the group's homepage and use the dropdown
> menu. This will also allow you to change your email
> settings to digest or vacation (no mail).
> //www.freelists.org/webpage/mso
>
> To be able to use the files section for sharing
> files with the group, send a request to
> mso-moderators@xxxxxxxxxxxxx and you will be sent an
> invitation with instructions. Once you are a member
> of the files group, you can go here to
> upload/download files:
> http://www.smartgroups.com/vault/msofiles
>
=== message truncated ===
__________________________________________________
Yahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
http://dir.remember.yahoo.com/tribute
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, send an email to
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe
Or, visit the group's homepage and use the dropdown menu. This will also allow
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
Other related posts:
