Database design question

[Stephan Ranocchia]

I'm in the process of designing a database and would like some
suggestion on how to architect it.

I have 3 'objects', frequency, task, skill.

For each task there is a frequency however for some tasks there are a
number of different skills, each of which have different frequencies,
so, for example, imagine frequencies being time in minutes. Each task
has to be done a certain number of times but certain tasks have varied
skill levels and each skill level has different frequencies associated
with it.

Task 1, Freq 5min
Task 2, Freq 10min
Task 3, Skill 1, Freq 5min
Task 3, Skill 2, Freq 15min
Task 4, Skill 1, Freq 10min
Task 4, Skill 2, Freq 15min
Task 4, Skill 3, Freq 20min

The problems is that the freq / skill is dependent on the task. I'm
having trouble deciding how to build this database.

I could just hard code the Tasks/Skill in a table so that I have

TaskID PK
Task
Freq

and have records like:

1 Task1 5
2 Task2 10
3 Task3Skill1 5
4 Task3Skill2 15

but I don't like it because it is not easily maintained - in case
freq/skills change.

Does anyone have any ideas. The important information is the
frequency as it will be used to build timetables.

[Anith Sen]

Re: Database design question

>> I have 3 'objects', frequency, task, skill. <<

Basic question - in your business model are these separate entities or
attributes of an entity? What are the dependencies among them? The answer to
these questions lay the foundation for the table design.
[color=blue][color=green]
>> I could just hard code the Tasks/Skill in a table ... <<[/color][/color]

That violates 1NF (assuming tasks & skills are different attributes) and
based on my interpretation of your requirements would cause an update/delete
anomaly. Generally database design cannot be accomplished using Newsgroup
responses since it requires a comprehensive understanding of your underlying
business model, rules & requirements. However, based on a series of
assumptions, here is one way of SQL representation :

CREATE TABLE Tasks (
Task_id INT NOT NULL PRIMARY KEY,
Details VARCHAR(30) NOT NULL,
....);

CREATE TABLE Skills (
Skill_id INT NOT NULL PRIMARY KEY,
Decription VARCHAR(40) NOT NULL,
...);

CREATE TABLE TaskSkills (
Task_id INT NOT NULL
REFERENCES Tasks(Task_id),
Skill_id INT NOT NULL
REFERENCES Skills(Skill_id ),
Freq INT NOT NULL
PRIMARY KEY(Task_id, Skill_id));

--
- Anith
( Please reply to newsgroups only )