September 6, 2005 at 12:21 am
looking for some advice. In reality this exists. Main groups such as ABC Inc. Within ABC Inc will be several offices (within various states ie. ABC Inc- Texas) within that will be employees for each office but the thing is that the employee may be a receiver of a service or a giver of a service or both. Sort of like a trainer or trainee. These individuals will have have specifics pertinent to their classification of trainer or trainee OR BOTH or even cataloguer or whatever. Trainers have to have completed certain training checks before being added to the searchable criteria and this has to be included in the db. Searches within the company will look to match trainee's with trainers as well as external individuals with these trainers and trainees, etc. Here is how I picture modeling this. The main group holds id and name of general company (ABC Inc)--- a one to many relates to the next table that holds the specifics for the ABC-Inc Texas office which in turn one to many(s) to the personal information of the specific people in the specific office. Now I thought to distinguish between the trainer or trainee or cataloguer or whatever they are, I would use the super-type/sub-type relationship to give me the one to one flexible relationship with each sub type (trainer, trainee, etc) or more than one sub type when the individual fits two or more categories which I can simply distinguish with a yes no in the individuals info table. Is this a smart approach or should I be venturing down another path. I would really appreciate the input....
September 6, 2005 at 10:33 am
G'day,
If I restate the requirements as a small set of distinct statement, I think I get the following. First question: Am I understanding the requirements correctly?
Companies have zero or more offices.
An office is located in a state.
An office has zero or more employees (What if an employee relocates?)
An employee has zero or more roles
An employee with a role has additional data
An employee must pass certain requirements before being assigned to a specific role
If the requirements are correctly understood, I would translate this to the following set of model assertions. There are multiple ways to model this set of requirements, each with their pros and cons.
Company is 1 to zero or more employee
Company is 1 to zero or more office
Office is 1 to zero or more employee
Employee is 1 to zero or more role
Role is 1 to zero or more training checks.
So far, things are fairly standard. Because employees can relocate, you may want to model the employee/office relationship as a "CurrentOffice" attribute of the employee. There are two interesting elements you will need to decide. One decision is how to model the employee/role relationship. Employee/Role/extra data may be modeled as a set of role specific tables, or as a single table with unused columns for roles that do not apply to the employee. Because roles can come and go for an employee, you may want to model this as an employee-> role join table, with subtables for each employee/role.extra data.
Hope this helps. Feel free to drop additional questions and we will be happy to offer further suggestions.
Wayne
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply