Advice Needed - Normalising tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Advice Needed - Normalising tables

Author

Topic

talasabz
Starting Member

11 Posts

Posted - 2002-07-21 : 17:39:28

Hi There,

Can someone please have a look at these tables..and tell me if they are wrong?

am suppossed to have 3 types of customers.
1- where the normal user gets registered..
2- organisations
3- host (offering temp rehoming services for pets).

I will be selling and rehoming pets.
are the tables below any good??

Many thanks.

Customer table:
Customer#, C_Fname, C_Lname, C.address1, C.address2, C.postCode, C.fax
C.country, UKlocation, C.Hphone, C.Mphone, C.e-mail, (rehoming-services?
yes/no)

Organisation details:
customer#, Org#, Org.website, Org.AreaCoverd,
Org.activities, Org.addinfo, Org.Logo

Members of Org details:
Member#, Org#, Member_name, Member_email, member_Phone, member_ext.

Host table
customer#, host#, animals accepted?, max_no_days, price_per_day

Pets table:
pet#, picture, p_type, p_breed

Permenant pets for rehoming
pet#, pet_perm#, name, p.color, p.d_o_b, age,
p.size, p.personality1, p.personality2, p.personalty3, p.vaccination,
p. allergies, microchipped.

pets for sale:
Pet#, sale#, no.ofMales, PriceperMale, No.ofFemales, PriceperFemale,

Kevin Snow
Posting Yak Master

149 Posts

Posted - 2002-07-22 : 17:17:35

It looks like you are going for 3rd normal form (Eliminating Columns not dependent on the table KEY)

I have some concern over the table 'Permanant pets for rehoming' You have columns Personality1,Personality2,Personality3 - Assuming these are related, they would be un-normalised [using the UK spelling]. You should consider a Personality table, or more descriptive column names.

I also wasn't quite sure about the 'pets for sale' table. The 'numberofmales' and numberoffemales' columns seemed to indicate the record was referring to a 'litter' (forgive me if the terminology isn't correct in all contexts). As long as Sale# applies to the 'litter' and not an individual pet, it is probably normalised properly.

You do have email addresses and phone numbers common to both the Customer and MemberofOrg tables, but splitting those out may reflect a higher level of normalisation than you want.

There's my two cents (a farthing?)

talasabz
Starting Member

11 Posts

Posted - 2002-07-22 : 17:58:40

Hi there,

Thanks for taking the time to answer my post.
I have had a look again at that past where I can get rid of the organisation's memebrs..
I will post it again my latest updates to the tables.
but certainly all u pointd out to me seem right:)

Thanks

talasabz
Starting Member

11 Posts

Posted - 2002-07-23 : 07:20:27

Hi again,

I made a few changes to the tables..
do they look any better now?

Thanks again:)
--------------------------------------

Customer Table:
Customer#, C_Fname, C_Lname, C.address1, C.address2, C.postCode, C.fax
C.country, UKlocation, C.Hphone, C.Mphone, C.e-mail,
(rehoming- services? yes/no)

Organisation Table:
Org#, OrgName, OrgAdd1, Orgadd2, Org_postcode, Org_Fax, Org.website, Org.AreaCoverd, Org.activities, Org.addinfo, Org.Logo, contact_Name, Contact_e-mail, Contact_phone, Contact_Ext.

Host Table
customer#, host#, animals accepted?, max_no_days, price_per_day

Pets Table:
pet#, picture, p_type, p_breed, p.d_o_b

Permenant pets for rehoming
pet#, pet_perm#, name, p.color, age,
p.size, p.personality1, p.personality2, p.personalty3, p.vaccination,
p. allergies, microchipped.

pets for sale:
Pet#, sale#, number_pets, gender, price, additional details.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources