I built a form where user enter their Product and Cost and this two pieces of information going to add in 3 different categories (Manufacturer, Manufacturing date and Expire date). There are total 20 different categories but users atleast enter information of 1 category and maximum 3 categories at a time. I don't know how to create the table where 2 pieces of information going to add in three different categories at once.
My idea is to make a parent table of 2 columns name Product and Cost, then make additional 20 tables for different categories but i don't know how the product and cost adds into these tables or how to interconnect each other .? Another method is to create 20 columns and then add multiple columns in a single columns which i don't know how to do that .?
Is this a good approach or is there any other way to make an efficient database based on these requirements. I am new to sql database. Any Idea how to do this .?
Very psuedo coded...20 table is pretty horrible to maintain.
Table Product:
ProductID
ProductName
Cost
Table Category
categoryID
categoryname
Table Product_category
ProductID
CategoryID
This will allow you to link a single product to an unlimited number of categories. 1 product to many product_categories to 1 category.