Trauma database - help with entity relationships

[GarthT]

Hi everyone

I am trying to write my first relational database and after reading a couple of books I am aware some of the principles but not sure I really understand them.

I am a plastic surgeon who deals with trauma patients. I am trying to create a database of patients we have treated. Each patient may have several wounds and each wound may undergo several procedures both in one sitting and also over time. A collection of procedures make up an operation. Thus a wound may have several operations (many to many) and I have resolved this by creating a join table called "plastic surgical procedure"- I think this logic is correct - but I would like to know it is!!

Similarly patients may be infected by organisms; have surgical complications and non-surgical complications - I have again postulated that these relationships are many to many and can be resolved by an intervening join table.

Finally, a wound may have injury types within it, eg. bone, vessel, etc., each with their own attributes and with a many to one relationship to the wound.

Have spent most of our recent sunny weekend trying to get my head around all of this I would be really grateful if someone could confirm my logic. Or tell me what to do if I have got it completely wrong!!

I have attached a .pdf of the ER diagram I have drawn

Thanks for any advice

Garth Titley

Plastic surgeon UK

ER_diagram2.pdf

[bcooney]

I think it looks great.

How different are the injury types? Do they share many of the same attributes? Could they be in one table, and have a join table between Wound and InjuryTypes?

[GarthT]

Hi Thanks for getting back to me - you would not believe how many hours I spent getting there!

I have attached a copy of the relationship graph showing the joins I have made. I don't think I can consolidate the injury types into one - but would be interested in knowing what you think

cheers

Garth

BAPRAS_v.2.pdf