Inserting data with MySQL

Question

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

*categ Material*
------------------------------
title           ID       Price
------------------------------

first item    | 123 | 1 195.00

second item   | 845 |   469.00

...

*categ Montage*

item          | 461 |   146.00

item number 2 | 821 |   654.00

third item    | 012 |   931.00

...

*categ MBC*

item          | 642 | 2 135.00

...

You can always store everything in a single table. Whether you want an efficient solution or not is a different matter altogether — hjpotter92
– hjpotter92, Commented Apr 27, 2014 at 12:43
I would like to store it in a single table and I will need to edit data later too. Sure I need the most efficient solution, but I don't know how to do it... What you would do in my situation? — falcon
– falcon, Commented Apr 27, 2014 at 12:53

Undisputed007 · Accepted Answer · 2014-04-27 12:43:28Z

1

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.

answered Apr 27, 2014 at 12:43

Undisputed007

6491 gold badge11 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

geoB · Accepted Answer · 2014-04-27 12:48:09Z

1

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.

answered Apr 27, 2014 at 12:48

geoB

4,7287 gold badges45 silver badges75 bronze badges

3 Comments

falcon Over a year ago

I would like to avoid creating large numbers of tables. What I should do? I have an idea to create 3 tables named material, montage, .... and then insert for any quotation ID itself items. Is it right way?

geoB Over a year ago

Avoiding large numbers of tables is absolutely the right thing to do. Consider the relationships between the entities represented by each table. For example, the category table has a one-to-many relationship with the item table. The relationship is established by the category_id field in item. Apply this concept to your tables.

falcon Over a year ago

Thank you geoB, I'm going to try it but it will take me a long time.

Gordon Linoff · Accepted Answer · 2014-04-27 12:48:26Z

1

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.

answered Apr 27, 2014 at 12:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

falcon Over a year ago

Thank you very much for some knowledge.

Collectives™ on Stack Overflow

Inserting data with MySQL

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related