Current Client/Prospective Client Database (1 Viewer)

Hi all

Brand new on here and desparate for some help and guidence.

So far with Access I have just used it as a store of addresses to mailshot prospective clients.

However, I now need a more complex database and this is where you might be able to help.

First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.

Does this mean I need to do three tables:

1/ "University Details" which gives the address details
2/ "Department" storing the departments of the unis we work for
3/ "Client" Name of the client(s) in that department.

After this how do I link them?

Thanks

Razieluk

Basically I think you have Clients and Contacts

So a Client table has Name address etc

Contact has say Name, Dept, Tele, Email with a foreign key included from tbl_Client. You need to consider NOW what other data you will want to keep before committing.

Len

But I thought that you shouldn't duplicate data?

In which case as I might have three people in the same department who use me for different jobs. So if I just had a "contacts table" I would be duplicating some information (as in the address and telephone number) three times!

Ashley

Correct.......but

1/ "University Details" which gives the address details
2/ "Department" storing the departments of the unis we work for
3/ "Client" Name of the client(s) in that department.

After this how do I link them?

Well you need a relationship between Department and Client Name because otherwise you would not know where they were

So the Contacts table has both Dept and Name so that together they form a Primary key. i.e Fred in Dept1 This cannot be duplicated but you must consider that Fred in Dept1 may exist in another University. Problem maybe

So maybe the email address is the best primary key but will they all have email addresses, perhaps not

So maybe PK is Client,Dept.Name.

Sometimes it is a judgement call which is why I said that you need to consider All data that you may wish to keep. Only then can you really decide what tables are required and their entities.

Foreigh keys do not really count as duplicated data since they will be linked to a PK and in all probability cascade update will be enabled along with referential integrity.

Look now at the bigger picture although you may not implement it all at this first stage

Len

Razieluk said:

But I thought that you shouldn't duplicate data?

In which case as I might have three people in the same department who use me for different jobs. So if I just had a "contacts table" I would be duplicating some information (as in the address and telephone number) three times!

Ashley

Click to expand...

Also this raises the issue of what details are you going to keep in terms of jobs because this will impact design.

You may have 3 people in a single dept for different jobs, but you may not !

Their telephone number may be the same but again it may not if they have individual extensions. main number comes from the Client detail.

You consider the "attributes" of a contact

They have name etc, coincidence will mean you have more than one Smith or Jones is that duplication ?.

Len