I'm not looking for solution to any specific problem, but rather would theoretically need advice on how to handle previously unknown count of data (rows).
Let's say I have a form divided into 3 categories (material, montage, minor budgetary costs).
Each category can have any count of items and each item has an id, a name and a price.
How should I store these data in MySQL database? Is it possible store it only to one table?
Thank you for any suggestion and direction.
Quotation with some ID
title ID Price
first item | 123 | 1 195.00
second item | 845 | 469.00
item | 461 | 146.00
item number 2 | 821 | 654.00
third item | 012 | 931.00
item | 642 | 2 135.00
You need to understand how normalization works. Try to normalize your data and from there you may know how much tables you need. It makes sense to have a table called Materials, but for the other forms, their usage are not understandable.
A normalized solution, using category & item as in the problem statement:
CREATE TABLE `category` ( `id` INT NOT NULL AUTO_INCREMENT, `category` VARCHAR(45) NULL, PRIMARY KEY (`id`)); CREATE TABLE `item` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) DEFAULT NULL, `price` double DEFAULT NULL, `category_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`));
You can have many items for each category. Each item row in the item table is "connected" to the category by the category_id. Create statements courtesy of MySQL.
You can store these in one table with four data columns. What you are calling different "tables" might be called "categories" or something. An example table structure would look like:
create table items ( ItemId int not null auto_increment, CategoryName varchar(255) not null, Title varchar(255), Id int, Price decimal(10, 2) );
This is an example. Depending on your needs, you might want to enforce that
CategoryName only takes on the values you want. The best way is to have a separate
Categories table with a proper foreign key reference. You could also implement this using enumerated types, a trigger that implements a
check constraint, or just dispense with the check altogether. Similarly, the data types are just guesses, and you might want other fields.