I have following tables (Its not actually accessories but I am using it as an analogy),

  • Accessories {AccessoriesId, Name, other accessory related attributes...}
  • ClientAccessories (ClientAccessoryId, AccessoriesId, CLIENTID, LastModified, CreatedDate, Username, isDeleted)
  • EmployeeAccessories (EmployeeAccessoryId, AccessoriesId, EMPLOYEEID, LastModified, CreatedDate, Username, isDeleted)

Now problem is that each Employee and each client will get a set of default accessories, which they can delete/update as well, which will set a flag in there specific table. They will add there own accessories as well in future.

I am not sure about if this is a valid design ?

  • 2
    problem is that each Employee and each client will get a set of default accessories You must have a table like ClientTemplate. While creating a client record you must copy all records from template table to entities table with proper ClientID settings (for example, in trigger). Then the client may modify its accessories set in entities table. – Akina Nov 7 '19 at 8:19

It might not be the most elegant model - but your description points to 2 (independent) m:n relations between Clients and Accessories and Employees and Accessories. The usual way to resolve those m:n relations IS to create mapping tables like you did - so nothing wrong from that view point. Of course it needs joins to request data over the 3 tables, when you want to know what accessories are still held by a certain Client or employee.

If your clients or employees receive the same initial sets of accessories you can go like @Akina wrote in the comments. Fill a small template table with the sets you want to give new clients / employees and copy the lines over - while filling in the correct clientID / employeeID to the corresponding mapping table.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.