[mso] Re: Database Normalization
- From: James LaBorde <jlaborde@xxxxxxxxx>
- To: "'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
- Date: Tue, 10 Sep 2002 10:09:15 -0700
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
*************************************************************
Other related posts:
