这个MySQL数据库可以改进或它是好的，因为它是？ [英] Can this MySQL db be improved or is it good as it is?

问题描述

在分类广告网站中，您有几个类别（汽车，汽车，房屋等）。
对于所选择的每个类别，隐藏的div都会显示，并显示用户可能指定的其他选项。

现在我创建一个db，我看了一下正常化的一些文章，使之优化等等...

这是我今天的上篮

类别表：

- 汽车

- mc

- 房屋

分类的表格：结果
- 标题结果
- 描述结果
- hide_telephone_nr结果
- 多变的结果
- 动作

- 价格

- modify_date

POSTER TABLE：

- 名称

- passw

- 电话

- 电子邮件

区域表：

- 区

- 社区

CARS表：

- 年

- fuel

- 齿轮箱

- 颜色

MC表：

- year < br>
- type

HOUSE TABLE：

- 别墅

- 公寓

- 大小

- 房间

等

我目前为每个类别一个表，约30张表。
是不是太多了？

我到目前为止还没有创建PK或FK，还有...

您能告诉我这个设置是否是好的，还是我的设置不一样？ b
$ b

此外，您将如何设置FK和PK？

解决方案

从我的理解，我会为所有类别创建一个表，并在其中存储类别的名称和ID。接下来，我将创建一个单独的表来存储每个类别的附加选项。

  MySQL表1 
 ---------------- 
 Category_ID int PRIMARY KEY 
 Category_name varchar 
 
 MySQL表2 
 ---------------- 
 Category_ID int 
 Entry_Number int PRIMARY KEY（这将跟踪所有属于哪个条目）
 Additional_Option varchar 
 Additional_Option_Answer varchar（这是存储您的用户单击/输入的那个）
  

例如，使用：

  POSTER TABLE：
  -  name 
  -  passw 
  -  tel 
  - 电子邮件
  

$ b b


您将在 Category_ID 中存储category_id，并存储 name passw tel code>在它自己的行中，并且这些条件的用户输入将存储在 Additional_Option_Answer 。

的海报的类别ID为1，地区的值为2。



如果第一个用户添加了以下内容，它看起来像这样：

  --- -------------------------------------------------- ---------------------------------------- 
 Category_ID | Entry_Number | Additional_Options | Additional_Options_Answers 
 --------------------------------------------- ------------------------------------------------ 
 1 | 1 |名称| doug 
 1 | 1 | passw | 1234 
  

如果第二个用户添加了某项内容，它将如下所示：

  ---------------------------------- -------------------------------------------------- --------- 
 Category_ID | Entry_Number | Additional_Options | Additional_Options_Answers 
 --------------------------------------------- ------------------------------------------------ 
 1 | 2 |名称| Hamlet 
 1 | 2 | passw | iliketurtles 
  

更多，让我们应用另一个类别：

 区域表：
  -  area 
  -  community 
 
 --------------- -------------------------------------------------- ---------------------------- 
 Category_ID | Entry_Number | Additional_Options | Additional_Options_Answers 
 --------------------------------------------- ------------------------------------------------ 
 2 | 3 |区域|旧金山
 2 | 3 |社区| community_name 
  




In a classifieds website, you have several categories (cars, mc, houses etc). For every category chosen, a hidden div becomes visible and shows additional options the user may specify if he/she wishes.

I am creating a db now, and I have read some articles about normalization and making it optimized etc...

Here is my layup today

CATEGORY TABLE:
- cars
- mc
- houses

CLASSIFIED TABLE:
- headline
- description
- hide_telephone_nr
- changeable
- action
- price
- modify_date

POSTER TABLE:
- name
- passw
- tel
- email

AREA TABLE:
- area
- community

CARS TABLE:
- year
- fuel
- gearbox
- colour

MC TABLE:
- year
- type

HOUSE TABLE:
- Villa
- Apartment
- Size
- rooms
etc

I have so far one table for each category, so that is around 30 tables. Isn't that too many?

I haven't created PK or FK for any of these so far, haven't got that far yet...

Could you tell me if this setup is good, or should I have it made differently?

ALSO, how would you setup the FK and the PK here?

Thanks

解决方案

From my understanding, I would make a table for all the categories and store the categories' name and ID there. Next, I would create a separate table to store the additional options for each category.

MySQL Table 1  
----------------   
Category_ID int PRIMARY KEY  
Category_name varchar  

MySQL Table 2  
---------------- 
Category_ID int   
Entry_Number int PRIMARY KEY (this will keep track of which entry everything belongs to)
Additional_Option varchar  
Additional_Option_Answer varchar (this is the one that stores what your user clicks/inputs)

For example, using:

POSTER TABLE:
- name
- passw
- tel
- email

You would store the category_id this data is for in Category_ID and store name passw tel email into Additional_Option in it's own row and the user's input for those criteria would be stored in Additional_Option_Answer.

Category_ID for Posters will be 1 and for Area will be 2.

It would look like this if the first user added something:

---------------------------------------------------------------------------------------------
Category_ID   |    Entry_Number    |    Additional_Options   |   Additional_Options_Answers
---------------------------------------------------------------------------------------------
       1      |   1                |    name                 |   doug
       1      |   1                |    passw                |   1234

It would look like this if the second user added something:

---------------------------------------------------------------------------------------------
Category_ID   |    Entry_Number    |    Additional_Options   |   Additional_Options_Answers
---------------------------------------------------------------------------------------------
       1      |   2                |    name                 |   Hamlet
       1      |   2                |    passw                |   iliketurtles

Further more, let's apply another category:

AREA TABLE:
- area
- community

---------------------------------------------------------------------------------------------
    Category_ID   |    Entry_Number    |    Additional_Options   |   Additional_Options_Answers
    ---------------------------------------------------------------------------------------------
           2      |   3                |    area                 |   San Francisco
           2      |   3                |    community            |   community_name

这篇关于这个MySQL数据库可以改进或它是好的，因为它是？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！