Database design question

Question

I have a many to many relationship involving entities: Departments and Scopes.

1) Departments. (Imports, exports)
2) Scopes. (Nationwide, International)

1 department can be related to many different scopes. And one scope related to many departments.

So far my many to many relationship table would be like this:

DepartmentId (Foreign key)
ScopeId. (Foreign key)

Each department/scope relationship will have a whole configuration, involving document types and batch codes etc.

(So for:

department 1/ scope 1/document type 1
department 1/scope 1/document type 2

And then for each document type there will be a number of different codes:

department 1/ scope 1/document type 1 /Code 1
department 1/scope 1/document type 1 / Code 2

department 1/ scope 1/document type 2 /Code 1
department 1/scope 1/document type 2 / Code 2

So I’m thinking in having the many to many relationship table (department/scope) as:

Id (autoincremental) (Primary key)
DepartmentId
ScopeId.

This “Id” would be a foreign key in another table.

Is this okay what I’m doing or am I breaking some best practice rule?

Thanks

UPDATE 1
I'm finding that I will have a number of different many to many relationship tables.

1) Defines which scopes are related to each department

Id
DepartmentId
ScopeId

2) Document type related to each scope within each department.

Id (Primary key, autoincrementable)
DepartmentScopeId (Foreign key, to 1)
DocumentTypeId (Foreign key to document type).

3) Codes related to each document type, which is in a scope belonging to a department.

Id (Primary key, autoincrementable).
CodeName (nvarchar(50))
DocumentTypeDepartmentScopeId (Foreign key to 2)).

I'm not that sure if I am overcomplicating things, or if this is a normal pattern within the database world.

I guess after doing this I could create a view, which would aid me in accessing the data e.g: Return all Codes, per document type, per scope, per department".

Would be great if I could receive some advice on if this would be the correct way to go about this.

Thanks

Answer 1

I also think this is the best practice. You can also add more information in that new table concerning the relationship (e.g. dateCreated, if that makes any sense). Also, if it is crucial, don't forget to set the couple (DeparmentId, ScopeId) as Unique to avoid duplicate assignments.