Users and "Favourites" on another table

[craigprice]

Hi, I have the current setup:

TBLAPPLINKS
ID AppName AppLink
1 Google http://google.co.uk

tblWorker
ID loginID
1 12345678

I want to take these two tables and have an auto building table that will add each login ID from tblWorker to a new form as a Field name alongside those from tblapplinks, something like I'll show below, is this possible at all?

New Table? Users from tblWorker populate
AppName AppLink 12345678 12345678 12345678
Google http://google.co.uk 1 1 0

the 1's and 0's represent check boxes, if a user has a 1 I'd have a query to return that app link for them, hope this makes sense

 

[plog]

Your jargon got in the way of your idea. I think I have a vague idea of what you want, but you tried to use terms you weren't familiar with and made it confusing. In non-databasespeak, this is what I think you want to do:

You want to keep track of what links users have.

Correct? If not, post again and tell us about the real-world system you are trying to model in your database. No database terms allowed.

If I was right, you need a junction table. Those are used to define many to many relationships (1 user can have many links, and 1 link can go to many users). The structure of this table would look like this:

UserLinks
UserLinkID, autonumber, primary key
AppID, number, foreign key to TBLAPPLINKS.ID
WorkerID, number, foreign key to tblWorker.ID

That's, it. Just 3 fields, not 2 + (however many records in tblWorker you have).

 

[craigprice]

This looks to be the right way I'm going, pretty new to doing anything more than just data tables haven't really worked with relationships.

The current setup is basically that in AppLinks there is a clickcount, this increases on each click by a user, I want to swap this to make it customisable so rather than listing the most commonly used links, the user can select the links they want to populate the list hence me saying like favourites.

I've never really done relationships and I'm also not sure on how I'd do a foreign key either?

Could you provide some further guidance? I can sort of get the jist of where to go, thanks for your help so far

 

[plog]

The process of setting up your tables is called normalization: https://en.wikipedia.org/wiki/Database_normalization

Read up on that link and then see if you can find some tutorials to work through.