Normal design for this data

Question

How can one best represent this type of data in a relational database?

1. There a variable number of a particular Machine each uniquely serialized by the manufacturer.

2. Each Machine will be set up by the user to do variable number of the tasks that this type of machine is capable of.

3. All the machines will select from the same task list which itself may be altered over time

4. Finally each task will have a time setting which will vary from one machine to another.

The final condition seems to mess up my attempts to normalize this.

Answer 1

Create a common table relating each object to each other, with that supplemental data.

MACHINE TABLE
ID, MachineID

TASK TABLE
ID, Task

MACHINETASK TABLE
ID, MachineID (FK to MACHINE.ID), TaskID (FK to TASK.ID), Time

Answer 2

Machines :: Id, SerialNumber

Id is the primary key.

Tasks :: Id, Description

Id is the primary key.

MachineTasks :: MachineId, TaskId

MachineId and TaskId are the composite primary key. MachineId is a foreign key referencing Machines.Id. TaskId is a foreign key referencing Tasks.Id.

ScheduledMachineTasks :: MachineId, TaskId, Time

MachineId and TaskId are the composite primary key. MachineId and TaskId are a composite foreign key referencing MachineTasks.MachineId and MachineTasks.TaskId.

Note that this will only allow to schedule each task only once per machine and requires further extension if one task may be scheduled multiple times per machine.