使用mysql Workbench设计EER图 [英] design EER diagram using mysql Workbench

问题描述

大家好，


我正在尝试使用mysql workbench创建一个EER图，从数据库开始。


这是一个汽车零件数据库dat我试图设计。我有以下五张桌子：


1.制作

2.型号

3.年份

4.正文

5.修剪


现在使用或不使用工作台创建它们很容易但是我试图在其中创建一些业务逻辑以避免重复数据一次又一次地通过创建关系和外键来实现。


Table Make有字段：

1. Id

2名称


表型号有字段：

1. Id

2.名称

3 .make_id（外键）


现在从上面我创建了一个模型和制表之间的一对多关系，因为一个品牌有很多模型（例如本田可能有公民和一致等。 ）但模型只有一个品牌。那个'y workbench将make id作为外键传递到表中。


表年有字段：

1. Id

2.year


现在dat每个模型可能有多年，一年可能有很多模型告诉我dat我必须在这里建立多对多的关系。所以我创建了年表和模型表之间的多对多关系。通过该工作台创建另一个名为year_has_model的表，其中包含以下字段：


1. model_id

2. model_make_id

3。 year_id


现在我知道每个模型可能有不同年份，一年可能有各种模型，我可以毫无问题地插入值。


随着我走得更远，我还有2张桌子，即身体和装饰。现在，一年中的模型可能具有不同的身体，并且一年中的身体可能具有不同的修剪水平。


这真让我生气。我不知道如何将它与我的桌子联系起来。 Wat是解决这个问题的最佳方法，因为速度是最重要的，我不会重复这些问题。


如果我在身体和年份之间建立了多对多的关系，那么body_has_year将不会给我任何有关模型和制作的信息，如果我在修剪和身体之间建立多对多的关系那么wat关于这些年和我如何与它相关？


我无法解决这类事情。我应该帮助我吗？


以下是生成的sql代码，供快速参考请：

展开 | 选择 | Wrap | 行号

解决方案

我认为你的一些表是多余的，特别是你的年表。我看不出那有用吗？无论如何，我认为如果模型包含未知数量的主体/修剪年份，请考虑存储一个字符串，该字符串可以作为数组传递并在以后解压缩。甚至是阵列的序列化。这是一个很长的帖子，而且已经很晚了，所以不完全明白你想要什么，但你可能能够调整我所说的来帮助你解决问题。

wat我想要的是数据首先我必须根据选择制作一个完整的车辆。类似这样的事情：


1. Combobox for Makes：所有品牌都将填充在该组合框中。

2. Combobox for Models：这将填充选择make的基础，如果make是本田那么组合框将拥有本田的所有型号。例如Accord，Civic，Civic Hybrid IMA等

3. Combobox多年：选择一个型号将使这个组合框充满可用的生产年份。就像本田思域可能在2009年，2010年，2007年都有生产年....实际上每年一个模型都有生产。

4. Combobox用于车身：现在选择一年将使这个组合框填充，就像Honda-Civic-2009车身风格是两厢车和本田-Civic-2000车身风格是Coupe，两厢车，轿车，Estate（组合框中的4个值）。

5.修剪等级组合框：现在选择一个车身类型的修剪水平将被填充，就像本田思域-2009-Hatchback一样，可用的修剪等级是1.8i VTEC，1.8i，1.8VTC， 1.8VTEC


所以，我想你现在应该得到它。我曾试图为这个问题制作一个ERD，但实际上并没有完成它。


年份和模型表会有多对多关系，因为一年有很多模型而模型有很多年，而制作和模型会有一对多关系，因为制作有很多模型而模型只有一个品牌。


但我如何与其他表格联系起来。或者如果某人有没有关系的更好解决方案，请告诉我。谢谢。

一张桌子怎么样：制作，模特，年份，身体和修剪。

它会很大，意味着你需要调查MySQL DISTINCT子句，但我认为它会更好。


然后你可以使用几个where子句，如：

展开 | 选择 | Wrap | 行号

Hi everybody,

I am trying to make a EER diagram using mysql workbench to start with a database.

This is a vehicle parts database dat im trying to design. i have following five tables:

1. Make
2. Model
3. Year
4. Body
5. Trim

Now creating them with or without workbench is easy but im trying to make some business logic in it to avoid repetitions of data again and again by way of creating relationships and foreign keys.

Table Make have fields:
1. Id
2. name

Table Model have fields:
1. Id
2. name
3. make_id (foreign key)

Now from above i created a one to many relationship to between model and make table because a make have many models (e.g. honda may have civic and accord etc.) but a model will have only one make. That''s y workbench passed the make id as foreign key into the table.

Table year have fields:
1. Id
2.year

Now dat each model may have many years and a year may have many models tells me dat i have to create a many to many relationship here. So i created many to many relationship between year table and model table. By doing that workbench created another table called year_has_model which has following fields:

1. model_id
2. model_make_id
3. year_id

Now i know that each model may have various years and a year may have various models and i can insert the values into it without any problem.

As i go further i have 2 tables left i.e. body and trim. Now a model in a year may have different bodies and a body in a year may have different trim levels.

This is really making me mad. im not getting how should i relate it to my tables. Wat is the best approach to solve this problem as speed is the most important thing and i dont wana repeat values.

If i create many to many relationship between body and year then body_has_year will not give me any info about the model and make and if i create many to many relationship between trim and body then wat about the years and how do i relate to it?

I am not able to solve this type of things. Please help me wat should i do?


Here is the generated sql code for quick reference please:

Expand|Select|Wrap|Line Numbers

解决方案

I think a few of your tables are redundant, especially your year table. I cannot see how that is useful? Anyway, I think that if a model contains a unknown number of body/trim years, consider storing a string which can be passed as an array and unpacked later. Or even serialization of an array. It''s a long post, and it''s getting late, so don''t fully understand what you want, but you might be able to adapt what I have said to help your problem.

wat i want is dat first i have to make a complete vehicle based on the selection. something like this:

1. Combobox for Makes: All makes will be populated in that combobox.
2. Combobox for Models: This will be populated on the basis of make selected, like if make is Honda then the combobox will have all models for honda. For example Accord, Civic, Civic Hybrid IMA etc.
3. Combobox for Years: Select a model will make this combobox filled with the possible year of production available against this model. Like Honda-Civic may have production year in 2009, 2010, 2007 .... infact each year a model have a production.
4. Combobox for body: Now selecting a year will make this combobox populated, like for Honda-Civic-2009 body style is Hatchback and for Honda-Civic-2000 body style is Coupe, Hatchback, Saloon, Estate (4 values in combobox).
5. Trim Level Combobox: Now on selecting a body type trim level will be populated, like for Honda-Civic-2009-Hatchback the trim level available are 1.8i VTEC, 1.8i, 1.8VTC, 1.8VTEC

So, i think now u should get it wat im asking for. I have tried to make an ERD for this approached but really not getting it done.

Year and Model table will have many to many relationship because a year have many models and a model have many years whereas make and model will have one to many relationship because a make have many models and a model have only one make.

But how do i relate other tables. or if someone has better solution without relationship please tell me. thank you.

How about one table with: Makes, Models, Years, Body and Trim.
It will be big, and means that you will need to investigate the MySQL DISTINCT clause, but it will be better I think.

You can then use several where clauses like:

Expand|Select|Wrap|Line Numbers

这篇关于使用mysql Workbench设计EER图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！