CODEWITHSUNDEEP
phpmysql
falcon
falcon

Reputation: 357

Inserting data with MySQL

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

...

Upvotes: 1

Views: 65

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 1

geoB
geoB

Reputation: 4704

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.

Upvotes: 1

Undisputed007
Undisputed007

Reputation: 649

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.

Upvotes: 1

Related Questions