multiple fields, find unused data?

[tweetyksc]

I'm not sure if I set this up most efficiently from the start but here's what I have:

1. Table with catalogs (catalognum, catalogname)
2. Table with users (user info, catalogs they can access)

Since some users have multiple catalogs, I created 4 fields* in the user table: cat1, cat2, cat3, cat4. Catalognum is input there.
*was there a better way to do this?

The main question:
How can I run a query to see which catalogs are unused?
I'm thinking I have to create a table with (users/cat1) then append (users/cat2), and so on that way I will have a table with username and catalogs and then compare with the catalog table looking for null.

It seems there should be a better way....

 

[Fuga]

Since some users have multiple catalogs, I created 4 fields* in the user table: cat1, cat2, cat3, cat4. Catalognum is input there.
*was there a better way to do this?

Don´t know if it´s better, but you could have made a link table with fields user and catalog and then search for the catalog that has no entries in this table (missmatch query).

Or maybe that´s what you were saying yourself? (a bit tired right now... )

Fuga.

 

[dcx693]

I agree with Fuga. Your data structure is not optimal. You need to have a many-to-many relationship between catalogs and users. The way to express that in the database is to use a join table.