Normalisation Query!!!

talasabz

here is the database...
any ideas how i can make it better?

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

Organisation details:

Orgid, Org.name, Org.address1, Org.address2, Org.postcode, Org.country
Org.Phone, Org.Fax, Org.Email, Org.website, Org.AreaCoverd,
Org.activities, Org.addinfo, Org.Logo

Members of Org details:

MemberId, OrgId, Member_name, Member_email, member_Phone, member_ext.

Pets table:
OR
PetId, (OrgId, CustomerId), p.NAME, P.type, p.breed, p.color, p.d_o_b,
p.size, p.personality1, p.personality2, p.personalty3, p.vaccination,
p. allergies, pic.

pets for sale:

Petid, saleID, no.ofMales, PriceperMale, No.ofFemales, PriceperFemale,

ps: i do have a feeling that the organisation and the owner tables should be combined... repettitive fields are not so good in database design..;((((

Anon

its ok to put a field which is a primary key
in one table in another a table(foreign key )
thats how normalisation works

if many owners belong to an organisation
then the organisation primary key should be placed in the owner table as a foriegn key along with its primary key etc...

paul

Anon

Ofcourse this is only if your owners are only ever going to be a part of a single organization. If your owners could be members of multiple organizations then you should build a table to hold all organizations by owner.

orgs_x_owners

org_x_owner_id int
owner_id int
org_id int

What I think about when desiging a table is what data do I need to make my table functional. And I start by only building the tables with the fields that will always get filled. Then I look at what is left over and try to figure out if these fields should be in the table or outside of it.

So looking at a simple address table I'd say that Address1, City, State, Zip are all required fields, since Address2 isn't always filled I don't put it in my first draft of the table. Now I look at what is left over and I see Address2 and Zip4 (sorry for the US Centric design, but it's what I know). Now looking at these 2 fields I see that it's not worth trying to build them into a separate table that will get filled only when they are needed and instead just add them to the address table. Finally now I need to attach this address record to a person/place/thing.

This is where the rest of the database design comes in. In some situations I'd just add these fields to another table since they are logically a part of that table. But what if I don't plan on always having an address for something, or if I have different tables that need address records, why would I recreate all of these fields for every table that needs an address (Customer, Supplier, Personel)? So in some cases it makes sense, at least to me, to have a centralized address table and just link it to the records that need it. And in this case I'd link my unqiue ID (I always have one on every table) to the corresponding table that needs it. So my Customer table would have an address_id field that holds the unique id from the address table where I put my Customers address. Ofcourse this doesn't take into account having multiple addresses for a single Customer.

For the multiple addresses for a single customer scenario I'd use a table like I showed above. Adding an address type field to tell me what address this is.