Best practice for hierarchical MySQL asset tracking

Question

Hi, 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.

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.

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.

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.

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

Answer 1

Quote:

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

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.

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.