0

I have a program that will assemble separate materials into one new material. What is the best practice for storing that kind of data? (i.e, a view or a table or something else?)

table1:

MATERIAL_ID MATERIAL_DESC
1 APPLE
2 SUGAR
3 CINAMON
4 PIE_CRUST

input: assemble material -->

-- APPLE

-- SUGAR

-- CINAMON

-- PIE_CRUST

output: material assembled -->

-- APPLE_PIE was created

I think APPLE_PIE should go back into the original table as a MATERIAL with a new MATERIAL_ID, but what should I do with the old data?

In some cases material will be able to be taken out of the assembly. When that happens, should it retain it's original MATERIAL_ID or just have a new one and let the old data be purged? I don't know what the best practice for this situation is. I would like to see something more practical than theoretical if possible.

TheSchmidly
  • 59
  • 9
  • 2
    Does this answer your question? [Bill of materials database model](https://stackoverflow.com/questions/17651424/bill-of-materials-database-model) – David Browne - Microsoft Jul 02 '21 at 15:35
  • To a degree, although it's difficult for me to follow the dialog and doesn't make what I need to do clear imo. That topic is more theoretical and I'm not good at translating theory to practice. – TheSchmidly Jul 02 '21 at 15:52
  • @DavidBrowne-Microsoft actually it does seem to answer my question pretty well I just need to study it a bit more thoroughly to understand it. – TheSchmidly Jul 02 '21 at 16:05
  • @TheSchmidly Giving a direct answer like *do this* doesn't allow you to learn something. So you definitely need to read the dialog in the linked post to decide and avoid all the problems discussed in it. – astentx Jul 02 '21 at 17:23
  • I would say to use a separate table. – OldProgrammer Jul 02 '21 at 20:41

1 Answers1

1

I'm supposing that you already know what material you'll create as result of the output, so you can just insert the new material in your material table and after that, insert the recipe in other table.

You can remove or add new materials to any recipe editing the material_recipe table.

Code sample below:

create table material
(
material_id number generated by default as identity,
material_desc varchar2(4000)
);

create table material_recipe (
material_id number,
recipe_id   number
);

--Existent materials
insert into material (material_desc) values ('apple');
insert into material (material_desc) values ('sugar');
insert into material (material_desc) values ('cinamon');
insert into material (material_desc) values ('pie_crust');

--New material
insert into material (material_desc) values ('apple_pie'); --id 5    
insert into material_recipe values (5, 1);
insert into material_recipe values (5, 2);
insert into material_recipe values (5, 3);
insert into material_recipe values (5, 4);

commit;
Kauan Polydoro
  • 38
  • 5