Database Design Issue

[joekiteire]

Hello Everyone this is my first post.

I am working through an exercise and I have created the a Normalized Logical Database design (See attached) and ER diagram for a pharmacy system.

My question is how do you represent a relationship entity with Attributes attached ? In the example I attached I have a Table called Prescribe with Trade_Name, PPSN, GP_Number and Date as PK's and FK's maybe this is wrong from an access design point of view but this is how my Logical databse design worked out.



Has anyone any suggestions about designing this in access based on the relationships in the ER diagram.

Hope it makes sense :-)

Kevin

[Matrix]

The ER diagram looks good to me. The tables doesn't seem to have all fields mentioned in the diagram.

[joekiteire]

Thanks for your reply.

Yeah sorry I forgot to add those columns into the attachment.
So to link the table and reflect the relationship should is it ok or good design to link two primary key fields ie Drug.Trade_name to Prescribe.Trade_Name ?

[joekiteire]

I also attached a picture Access DB relationships based on the Diagram and Logical databse design. Are hte relationships setup right ? (The relationship between Doctor and Patient is 1 to M but I don't think My Access setup shows that.

Kevin

[Matrix]

Sorry, the table is too small and I didn't look close enough. Don't use name (text field) as PK or FK, always give it an ID field. By this way, you can avoid lots of problems.

[joekiteire]

Is this easier to see.. Its just relationships n Access that I don't think I have right..

[Matrix]

I usually will add unique ID (1, 2, 3...) for each table, then use this id to link the tables. e.g.

The Drug table will be like this:
id,
name,
year_made,
....

In Prescribe:
id (Optional, maybe the same with PPSN)
PPSN,
GP_ID, (or GP_NUM)
drug_id, (connect to the 'id' in drug table)
....

Hope you get the idea.