-2

I have task to do some task in some items, and the same task for the same item, can be done in different periods (every 1 day, 2 days... etc). Also In need some material for this tasks, that it depends on the task, but some some tasks in some periods don't need material in another cases it needs.

So my first idea it would be to have this table:

TaskOfItemsOnPeriodWithMaterials
{
    IDItem;
    IDTask;
    IDPeriod;
    IDMaterial;
    AMountOfMaterial;
}

In this case, the primary key would be IDTiem, IDTask, IDPeriod, IDMaterial.

But if IDMaterial is part of the key, it can't be null, so I couldn't have task in items in one period that doesn't have materials because it is needed.

So I think if IDMaterial has to be primary key, because in fact it is a condition of unique value, perhaps I need another table, something like that:

TaskOfItemsOnPeriod
{
    IDItem;
    IDTask;
    IDPeriod;
}

So I had to maintain two tables.

But I am wondering if it there would be a better solution.

Thanks.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Álvaro García
  • 18,114
  • 30
  • 102
  • 193
  • 2
    Tables have _columns_, not fields. (A date has a year field, a month field and a day field.) – jarlh Nov 12 '20 at 15:54
  • "In this case, the primary key would be IDTiem, IDTask, IDPeriod, IDMaterial." A table may only have one primary key. Are you saying that you are going to concatenate the values from the fields to *create* a primary key in a new field, or are you trying to decide which of these to use as primary key? If I understand you correctly, you probably want to create a primary key concatenating IDTask with IDItem, as it appears that period is non-unique and material can be null. This will allow you to have only one table, albeit with another field (column) added. – Greg Nov 12 '20 at 16:04
  • Right, I wanted to mean that I was thinking in a primary key that is the four columns, IDItem, IDTask, IDPeriod and IDMaterial. I considerate IDPeriod as part of the primary key because a taks for an intem can be in many periods, and each period could need difertent materials. It is why at first I was thinking that the four columns have to be part of the primary key. – Álvaro García Nov 12 '20 at 16:10
  • 1
    @Greg A table can have multiple columns as primary key, a [composite key](https://stackoverflow.com/questions/1110349/how-can-i-define-a-composite-primary-key-in-sql). – Schwern Nov 12 '20 at 17:07
  • Does the task need items and materials? Or does a task need item/material pairs? For example, does a task need 5 sheep, 10 wool, the sheering machine every day, and the sawmill on weekends? Or does a task need 5 sheep with the sheering machine every day, plus 10 wool with the sawmill on weekends? – Schwern Nov 12 '20 at 17:22
  • Or is it "every day the task needs the sheering machine with 5 sheep, and on weekends the task needs the sawmill and 10 wood"? If so, can a task/period have multiple items and materials: "Every day the task needs the sawmill and the lumber truck with 10 wood and 5 petrol"? – Schwern Nov 12 '20 at 17:34

1 Answers1

1

Let's state the problem a bit more formally.

  • "some task in some items" a task has many items, and I presume an item can have many tasks: many-to-many
    • "the same task for the same item, can be done in different periods (every 1 day, 2 days... etc)" the task can only use that item during a certain period
  • "I need some material for this [task]" a task has many materials of a quantity, and presumably a material can have many tasks: many-to-many
    • A task needs an amount of material

Our data tables are...

  • tasks
  • items
  • materials

The details of these tables don't matter. All we care is they have a primary key, and let's say they have a name for example purposes.

We need two many-to-many join tables. One to relate items with tasks and one to relate materials to tasks. These are not pure join tables, they have extra data.

  • task_items
    • A time period when it can be done
  • task_materials
    • Quantity of material needed for the task
create task_items (
  task_id bigint not null references tasks(id),
  item_id bigint not null reference items(id),
  -- How the period should be stored is another question
  -- and depends on the database.
  period text not null
);

create task_materials (
  task_id bigint not null references tasks(id),
  material_id bigint not null references materials(id),
  quantity integer not null check(quantity > 0)
);

And that should do it. To add items and materials to a task...

-- Task 10 needs 5 of material 20 and 3 of material 40.
insert into task_materials (task_id, material_id, quantity) values
  (10, 20, 5),
  (10, 40, 3);

-- Task 10 needs item 5 every 2 days, and item 10 every day.
insert into task_items (task_id, item_id, period) values
  (10, 5, '2 days'),
  (10, 10, '1 day');

To find, for example, all the materials and quantities needed for a task...

select t.name, m.name, tm.quantity
from task_materials tm
join materials on m.material_id = tm.material_id
join tasks on t.task_id = tm.task_id
where tm.task_id = 1234
Schwern
  • 153,029
  • 25
  • 195
  • 336
  • I _think_ the questioner intends to say that in some periods, the task needs a material and in other periods, the task does not require that material. – Neville Kuyt Nov 12 '20 at 17:26
  • 1
    @NevilleKuyt It is unclear. It might all revolve around the time period? I've asked for clarification. – Schwern Nov 12 '20 at 17:29
  • @NevilleKuyt is righit. I wanted to mean that in some periods the same task can need different materials. – Álvaro García Nov 23 '20 at 15:10
  • @ÁlvaroGarcía I posted made a few comments asking for a solid example. If you answer them, I can take another crack at the answer. – Schwern Nov 23 '20 at 21:26