sublcass table

[monkeymafia]

hello

i have 2 tables: employee and volunteer.

ive made volunteer a subclass of employee as volunteers are given a temprorary employee ID. this seemed to make sense.

as a result i have the following tables:

EMPLOYEE:
employee no (PK)
job title
address
home tel
mobile number
gender dob

VOLUNTEER
Emp No(PK)
arrival date
leave date
allocated room
passport No
insurance name
policy number
insurance tel no

does this look okay? i think it would cause problems though. for example if i want to do a SQL query showing how many volunteers are working at the company. not to sure how i would present it. maybe passport number needs to be a primary key too? confused lol

any help would be greatt!
thanks

[exterminator]

First, lets talk about the hierarchy and the attributes you have mentioned.

Don't employees have a passport number, insurance name and policy number, insurance tel number, leave date and allocated room? It does not make sense for an employee to not have a passport number. We will talk about uniqueness of passport numbers later!

What is the actual and complete difference between an employee and a volunteer employee? Just that the volunteer will be there for a fixed date after which he may be removed from company (which can happen before or after that date even..isn't it?

Why not just have one employee table and have an additional attribute "is_volunteer" to distinguish between them? (This is considering all other attributes are valid for both or some may be NULL)

[monkeymafia]

hi thanks for the reply.

for employees the system needs to record their employee number (unique), address, telephone number (home and mobile), gender, dob, and job title.

for volunteers the system needs to record the same as above plus: passport number, email, insurance details(company name, telephone and policy number). also need to record the arrival date and leave date. and specialised skills(which is a multivalued attribute) volunteers share a bungalow so the system needs to record which room has been allocated to them. so yes passport number etc. is only needed for volunteer.

each volunteer is given a temporary employee number while they are working there. thats why i thought i should make volunteer a subclass and re name "employee number" to volunteer number? not sure if that makes sense. yes thats right when they leave they are removed from the system. it only needs to know current volunteers.

so if i do put employees and volunteers in one table there will be alot of NULLS, but im not sure whether that would mattter?

thanks again