Have Geek Brain

Best practice for hierarchical MySQL asset tracking

I am writing an online project resource tracker but I am new to MySQL. What would be the best way to track projects, users, and assets for something like this? I have 3 tables for assets, users and projects. Users must have projects and assets. Assets can be members of multiple projects, and projects must be available to multiple users.

The first method I defined is to have a middle text field for each project with an ID for each asset it is associated with. Each asset will also have a middle text that each project ID it is associated with will have. This is a problem though, as I cannot search without having to parse the text to see what projects / assets it has connected.

Another solution without parsing would be to have separate tables for binding information, so for example there would be an assets table with resource id, project id and user id that it is part of, and if assigned to another project or user, there will be another entry into this table. This solution, however, will have multiple-entry assets.

Another way to do this is to create a site to create a table whenever a project is created and will store information about assets and users. Since there can be thousands of projects, this will grow the database very quickly and as far as I know, building MySQL on MySQL is more than records.

I am leaning towards the second solution. Is there anyone out there who knows a better way?

0


a source to share


1 answer


Quote:

there is a middle text field on each project with an identifier for each asset that it is associated with

This is the worst design ... maybe ever! Become familiar with Database Relationships . Take an emergency circle course . Take a look at some sample databases ; MS Access has some pretty decent templates for you to look into.



What you are describing looks like it can be modeled with this relationship:

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

The many-to-many relationship will go to a separate table.

+2


a source






More articles:


All Articles