当前位置: 动力学知识库 > 问答 > 编程问答 >

php - 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

...

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: