M:N relation in sql table

Question

I'm trying to figure out the best way to setup some tables in my database. I will be saving data about users in one table and navigations in another. I will simplify the tables.

I have two tables at the moment, User that has User_ID and User_Name. Navigation that has Nav_ID and Nav_Name.

What i'm thinking about is that Every user can have there "own" navigation counter. So basicly what i'm trying to do is that the Nav_ID counter will start on 1 for every user.

The way it works now is that every user uses the same navigation table and the Nav_ID counts from 1 for everyone.

I'm thinking that i need to make a 1:n to n:1 table in the middle to connect the User and Navigation tables together. Or is there another way, heard something about surrogates or something but i dont know if its a better way.

Answer 1

For a one-to-one relationship where each user has one navigation record, or a one to many relationship, where each user has multiple navigation records (but each navigation record only links to one user):

Your Navigation table might look like this:

Nav_id, User_ID, Counter

I don't think there's any need for a third table.

Navigation.User_ID is a foreign key to User.User_ID.

User
User_ID   Name
      1   Marcus
      2   David

Navigation
Nav_ID     User_ID     Count
     1           1         1
     2           2         1

Though, for a one to one relationship, if navigation is just one field, you could even just store it as a field in User:

User
User_ID    Name     Navigation_Count
      1    Marcus                  1
      2    David                   2

You'll only need a third table if there is a many to many relationship, where you have users and navigation records, but each user may link to multiple navigation records, and users may share the same navigation records.

Answer 2

To the best of my knowledge there is no other way to model Many To Many relationships.

Answer 3

If nav_id starts at 1 for every user and each users navigation records are related to that user you have a weak table. The relation will be 1:N from users to navigation. The primary key for navigation will be the compound key: user_id, nav_id. You will likely have to build custom code to increment the nav_id for each user.

You could have a single column surrogate key on navigation which auto-increments across all users. This would be the primary key on the navigations table. However, I don't think this would be useful. You will still need to implement the natural primary key as unique constraint.