daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Doing an assignment for uni... Developing and inplementing a Database Apllication.
What i got now:
Employee: (Employee id, Title, Name, Surname, 1st Address line, Postcode, City, Contact number, Emergency contact number, NI number, Email, Date started, Date finished, Position)
Salaried: (Employee ID, Salary)
Doctor: (Employee ID, Speciality)
Nurse: (Employee ID,Degree/Level)
Admin: (Employee ID, Function)
MedicalDirector: (Employee ID )
Manager: ( Employee ID )
HourlyPaid: (Employee ID, Hourly rate)
Ancillary: (Employee ID, Specialism, Training, Education)
Porter: (Employee ID, NVQ completed, Experience)
Cleaner: (Employee ID, Qualifications)
Basicly what i need now is come up with if few more atributes for these entities. Personally I think There should just be one table for employees, and deal with empty fields....
|
gavin18787
Premium Member
Registered: 22nd Feb 05
Location: Basildon, Essex
User status: Offline
|
So what do you need more people that work within a hospital?
There are quite a few more specifics if thats what you are after ie pharmacists, pathologists midwives etc
Drives supercharged Tec with torque  
|
Tom J
Organiser: South Wales
Premium Member
Registered: 8th Sep 03
Location: Bridgend
User status: Offline
|
Doctors would be split up into Registrars. Senior House Officers, House Officers. Bleep Numbers for all of these.
Don't know how detailed you want to go but each speciality will have these e.g
Trauma Reg. SHO, HO, On call staff
Urology Reg. SHO. HO. On Call etc etc
if you want to ask more just say (work in a hospital)
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
I need more specifics. Not "values" though, Just general name for it...
So i got Specialism for doctor which could be dentist, physiotherapist etc... But what other data Unique to for example A Cleaner I could store?
|
Tom J
Organiser: South Wales
Premium Member
Registered: 8th Sep 03
Location: Bridgend
User status: Offline
|
as gav said you have pharmacists, lab technicians, radiologists, midwifes, nurses, occupational therapists, physios, dieticians etc etc
also nurses split up into Sister, Ward Manager, Senior Nurse, Qualified Nurse, Nursing Assistants
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Thanks for the info!
I am most strugling with others though. Can't think what information would be specific for Medical director/cleaner/anclilary, porter
|
Aaron
Member
Registered: 9th Aug 04
Location: Cottingham, East Riding
User status: Offline
|
Good luck with the assignment. When i did my database assignment, i fooking hated it  ours was on a car rental place. What uni/college at you at?
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
I am Leeds Met. I hate the ERD's and shit. I am a hands on guy.
For my next years options dont think i will have a single written assignment
[Edited on 21-03-2011 by daymoon]
|
Tom J
Organiser: South Wales
Premium Member
Registered: 8th Sep 03
Location: Bridgend
User status: Offline
|
you have different levels of porter in my hospital, reception porter, helipad porter, general porter, xray porter, theatre porter, pay grades differ
|
Aaron
Member
Registered: 9th Aug 04
Location: Cottingham, East Riding
User status: Offline
|
quote:
Originally posted by daymoon
I am Leeds Met. I hate the ERD's and shit. I am a hands on guy.
For my next years options dont think i will have a single written assignment
[Edited on 21-03-2011 by daymoon]
Small world, mine was Leeds Met too Business Information Technology by any chance?
|
Tom J
Organiser: South Wales
Premium Member
Registered: 8th Sep 03
Location: Bridgend
User status: Offline
|
cleaner- ward area that they work on. any disabilties, e.g not able to use bleach
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
Right, and how could i call a field on a form where their level would be stored? or would "Level" be what is used in hospital terms?
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
quote:
Originally posted by Aaron
quote:
Originally posted by daymoon
I am Leeds Met. I hate the ERD's and shit. I am a hands on guy.
For my next years options dont think i will have a single written assignment
[Edited on 21-03-2011 by daymoon]
Small world, mine was Leeds Met too Business Information Technology by any chance?
Nah,Just computing. I take it you know Sanela then?
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
That seems an awful lot of tables for what could be compressed into a a few, i mean it'll take 2 JOIN's to get a doctors hourly rate...
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
quote:
Originally posted by Dom
That seems an awful lot of tables for what could be compressed into a a few, i mean it'll take 2 JOIN's to get a doctors hourly rate...
I kinda Agree about having so many tables
But doctors are salaried, so only one join!
I am thinking of getting rid of medical director at the moment..
Should see our ERD.. I just absolutely hate it!
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
quote:
Originally posted by daymoon
I am Leeds Met. I hate the ERD's and shit. I am a hands on guy.
If you're so hands on, make it. Perhaps then you will see that you need to learn more about how to plan a database because that's currently a bit of a dog's dinner.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
I know it is shit. But i am not on my own. There are 5 of us doing this project.
I said that there should be one employee table and just have few empty fields.. wont be more than 200 ish employes so not going to affect the perfomance of the system that much. When i say that it just gets ignored....
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Because it's a shit idea.
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
quote:
Originally posted by Ian
Because it's a shit idea.
what would you do in this case then? keep all tables? I am just strugling to come up with entities for each table, although Tom gave me some good info!
|
John
Member
Registered: 30th Jun 03
User status: Offline
|
When questions like this are asked you can always tell what's happening.
They don't care what you call the doctors, it's got absolutely nothing to do with the question.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
Personally i'd split it all into two tables, Contact_Info (basically you're employee tables minus start/end date and position) and Held_Position (enumeration column for job position, then columns for func/speciality, salary, hourly rate, education, qulaifications etc etc).
Edit - Scrub that, it looks messy as well.....
[Edited on 21-03-2011 by Dom]
|
daymoon
Premium Member
Registered: 1st Aug 08
Location: Selby, North Yorkshire
User status: Offline
|
quote:
Originally posted by Dom
Personally i'd split it all into two tables, Contact_Info (basically you're employee tables minus start/end date and position) and Held_Position (enumeration column for job position, then columns for func/speciality, salary, hourly rate, education, qulaifications etc etc).
I like the sound of it!
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
quote:
Originally posted by daymoon
quote:
Originally posted by Dom
Personally i'd split it all into two tables, Contact_Info (basically you're employee tables minus start/end date and position) and Held_Position (enumeration column for job position, then columns for func/speciality, salary, hourly rate, education, qulaifications etc etc).
I like the sound of it!
Only issue is the enum of job position, if you need to add another job then you have to edit the table structure. In which case remove the enum, create a third table containing job position and auto_inc ID and reference that from Held_Position.
Still think it's a bit of a shite design though as it'll take 2 joins to get an employees position, no doubt there is a lot lot better way of doing it.
[Edited on 21-03-2011 by Dom]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Employee: (Employee id, Title, Name, Surname, 1st Address line, Postcode, City, Contact number, Emergency contact number, NI number, Email, Date started, Date finished, Position)
Contact numbers need normalising, with a field for what type the number is. You could put a relationship in there as well and have a table of number types. That way you could query to see all numbers, WHERE on the JOIN to just get emergency etc.
I would personally normalise address, city, postcode also in to another table so your employees can have multiple addresses. In this case you would then need start and end dates for the address.
Position as well is fairly redundant if you have a positions table (you currently half do). Plus a position field in the employees table means you can only store one position concurrently. So you can't have multiple responsibilities and you can't store a history of positions. I would normalise for the latter reason alone.
Salaried: (Employee ID, Salary)
How many salaries does a person have? If it's one, this can be a field in employee. If it's more than one, see my comments on history of positions.
Doctor: (Employee ID, Speciality)
If this list of specialities is finite then this table should be a many to many junction between the employee and the speciality.
Nurse: (Employee ID,Degree/Level)
Ditto this. Otherwise you are writing Dip HE, Dip HE, Dip HE in every row. If that were normalised it would be less prone to error, take up less space, leave no room for anomalies and could be also described using the institution which awarded it if you want to know that.
Admin: (Employee ID, Function)
If this not the same table as the doctor speciality one? Looks the same to me. Different rows from the many to many but the relationship is identical.
MedicalDirector: (Employee ID )
A row in here indicates they are one? Again, can't this be a row in the specialties table which you then have an entry in the junction table if this is already the case?
Manager: ( Employee ID )
Ditto
HourlyPaid: (Employee ID, Hourly rate)
Probably keep this and add dates for when the rates changed. From a year end / taxation / pension point of view you would need to know things changed to total things up.
Ancillary: (Employee ID, Specialism, Training, Education)
Ancillary staff have only one piece of education? That wants normalising surely?
Porter: (Employee ID, NVQ completed, Experience)
Ditto
Cleaner: (Employee ID, Qualifications)
Ditto
Probably could do more and if you were in my class I probably would. In fact if my students came up with that I would bollock them first for not reading a book before proceeding.
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
quote:
Originally posted by Dom
In which case remove the enum, create a third table containing job position and auto_inc ID and reference that from Held_Position.
Still think it's a bit of a shite design though as it'll take 2 joins to get an employees position, no doubt there is a lot lot better way of doing it.
That's not a shite design, that's a properly normalised data structure. In practice if you were massively worried about query times you may bodge something as you suggest to reduce the expense of the cross table queries but no such concern exists here.
JOINs are your friend if you want good structure, not something you should be afraid of.
If you want speed, index them.
If you want more speed, bodge them out. But that is the last resort.
|
