I have a ruby/mysql question inquiring what the database structure best practice is:
A – The set-up I have 2 objects, (e.g., employees and documents), in this example, I would like to allow some employees to access other employee’s documents. The access should also have permission levels (view, edit, full).
Employee:
class Employee < ActiveRecord::Base
has_many :documents, :dependent => :delete_all
end
and Document:
class Document < ActiveRecord::Base
belongs_to :employee
end
B – The options
I am trying to figure out which option (or another not listed) is the best practice for database design. I see two options: (if there is another let me know)
Option 1) Employee could have linked documents. (Table A linked to Table B)
In this case, one employee is adding the document to another employee (i.e. the one employee is editing another employee)
The employee can have linked documents. (simple one-to-many relationship) For data structure, I could follow this example:
Rails Many to Many with Extra Column
In the documents controller, index, I could select all documents, via something like:
Select employee.documents, and employee.linked_documents, two datasets accessed as properties from the employee object.
Option 2) Documents could have linked employees. (Table B linked to Table A)
In this case, the document is edited, granting access to another employee. The Document would have many linked employees (simple one-to-many relationship).
Like above the data structure would be like the linked article.
The select statements are not as clean. The initial select can be the same, select employee.documents, but the other would be a separate call of the documents object, select documents.where(document.linked_user = current user).
C – My efforts
I can get either to work, but I don’t know what the database best practice is. Like store enums as integers, or only store data in one place sorta thing.
D – The Ask:
Which (if either) is the database design best practice, option 1, or option 2? (not looking for an opinion, looking for what other database admins have determined is standard (best practice) for when joining tables.)