1

I've read that when designing a simple many to many table, the best way to deal with primary key is to set it over both two columns. That's how I did that in table which assigns users to workgroups. But now, I've more complicated table, which describes, what permission have a user or a workgroup when accessing shared FSO (file system object = folder or file). I've used solution from this answer so I'm using two columns for specifyin' FSO and two for specifying allowed user or workgroup. My table looks like this:

id INT (primary key)
shared_file INT (foreign key)
shared_folder INT (foreign key)
allowed_user INT (foreign key)
allowed_workgroup INT (foreign key)
permission TINYINT
CONSTRAINT CHECK (shared_file IS NOT NULL OR shared_folder IS NOT NULL)
CONSTRAINT CHECK (allowed_user IS NOT NULL OR allowed_workgroup IS NOT NULL)

But now I come up with the idea that I don't actually need an extra column for id, basically this table is also many to many. But I don't know if I should set primary key over all 4 columns or how to do it to preserve consistency.

F. Korf
  • 95
  • 7
  • Use some kind of visual database tool like NaviCat or similar to deal with more complicated databases, it will save you a lot of time – Konrad Oct 05 '17 at 11:52

1 Answers1

1

Keep it very simple. Do not mix normalisation concepts with business/application requirements. Its best to have an integer based, auto incremented column as primary key in each table and reference it in other tables.

If you are required to have a check on uniqueness of combination for said columns, you should rather have a composite index with unique constraint. Business or application requirements keep on changing. You wouldn't want to make changes in the primary key when such times come.

Ketan Patil
  • 1,222
  • 13
  • 21
  • I've read [here](https://stackoverflow.com/questions/2190272/sql-many-to-many-table-primary-key#answer-2190294) that it's unnecessary to have a column like that in tables, which only connect other tables. I am usin' id column in every table where a record describes an object - folder, workgroup, user... But here it doesn't really make sense for a record describing permission, to have an id. I'm sure that business requirements won't change and if, I'm ready to deal with this changes, but now I wanna have a clean table schema. – F. Korf Oct 05 '17 at 17:23
  • In that case, you are absolutely right that there is not much use of the `id` column. I can only try to convince you by saying its a good practice. – Ketan Patil Oct 05 '17 at 19:21
  • @F.Korf you might want to check ORM (if you are using any) support for composite primary keys before going ahead with this. – Ketan Patil Oct 05 '17 at 19:22
  • No, I'm not using any ORM. So without an extra id column, I will set `PRIMARY KEY (shared_file, shared_folder, allowed_user, allowed_wg)` But will it preserve consistency? With combination of those two constaint checks mentioned in my question, is it true, that there can only be specified permission on particular file for particular user once? But what about NULLs? Will be for example this set of data valid? `(shared_file, shared_folder, allowed_user, allowed_wg, permission) (1, NULL, 1, NULL, 1), (1, NULL, 2, NULL, 1), (1, NULL, NULL, 1, 1)` – F. Korf Oct 05 '17 at 19:51
  • It wont work if you have null values. Have a look at https://stackoverflow.com/questions/386040/whats-wrong-with-nullable-columns-in-composite-primary-keys – Ketan Patil Oct 05 '17 at 19:55
  • OK, than I will need to go with an extra id column – F. Korf Oct 05 '17 at 20:31