分层MySQL资产跟踪的最佳实践

I'm writing an online project asset tracker but I'm new to MySQL. What would be the best way of tracking projects, users, and assets for something like this? I have 3 tables for assets, users, and projects. Users should own projects and assets. Assets could be members of multiple projects, and projects should be able to be seen by multiple users.

我正在编写一个在线项目资产跟踪器,但我是MySQL的新手。对于类似这样的事情,跟踪项目,用户和资产的最佳方法是什么?我有3个资产,用户和项目表。用户应拥有项目和资产。资产可以是多个项目的成员,项目应该能够被多个用户看到。

The first method I figured would be to have a mediumtext field on each project with the id for every asset that it's linked to. Each asset would also have a mediumtext that will have every project id it's linked to. This is a problem though, since I can't really do a search without having to parse the text to find out the projects/assets it's attached to.

我想到的第一种方法是在每个项目上都有一个中间文本字段,其中包含与其链接的每个资产的id。每个资产还有一个中间文本,它将链接到每个项目ID。这是一个问题,因为我不能真正进行搜索而不必解析文本以找出它所附加的项目/资产。

Another solution without parsing would be to have separate tables for the linking information, so for instance there would be an asset table with the asset id, project id, and userid that it's part of, and if it gets assigned to another project or user, there would be another entry into that table. This solution, though, will have assets that have multiple entries.

没有解析的另一个解决方案是为链接信息提供单独的表,因此例如,将有一个资产表,其中包含资产ID,项目ID和用户ID,如果它被分配给另一个项目或用户,会有另一个进入该表的条目。但是,此解决方案将具有包含多个条目的资产。

Another way of doing it would be to have the site create a table whenever a project is created, and that will store the asset and user information. Since there might be thousands of projects, this will crowd up the database pretty quickly, and creating tables is heavier on MySQL than entries, as far as I know.

另一种方法是让网站在创建项目时创建一个表,并存储资产和用户信息。由于可能有数千个项目,这将很快占用数据库,据我所知,创建表格在MySQL上比条目更重。

I'm leaning toward the second solution. Is there anybody who knows a better way?

我倾向于第二种解决方案。有没有人知道更好的方法?

1 个解决方案

#1

Quote:

have a mediumtext field on each project with the id for every asset that it's linked to

在每个项目上都有一个中间文本字段,其中包含与其链接的每个资产的ID

This is the worst design... maybe ever! Read up on database relations. Take an emergency crash course. Look at some example databases; MS Access has some pretty decent templates you could examine.

这是最糟糕的设计......也许永远!阅读数据库关系。参加紧急速成课程。看一些示例数据库; MS Access有一些你可以检查的相当不错的模板。

What you describe looks like it could be modelled with these relations:

您所描述的内容看起来可以使用以下关系建模:

  project --- inf:inf --- users
  asset   --- 1:1     --- users
  asset   --- inf:inf --- projects

The many-to-many relations would go in a separate table.

多对多关系将放在一个单独的表中。

#1