Relational database Design- Relationship problem

Question

Hi I need to create database tables to store memo. in the "To" clause user may select individual employee or group of employees (Different groups are already available in database having many to many relationship with employee). i am wondering what should be structure of tables. For simple memo where there are no groups i will have "MemoMasters" and "Memodetails" with memodetail having EmployeeID as foreign key. how could i embed groups in this structure

Regards

Answer 1

In this structure you could not.

This is a many to many relationship, model it as such.

EDIT (reviewing the scenario): The above requirements are not enough to determine the 'exact solution'

At the risk of overly simplifying, you should:

1) check if you can stick the new information into any existing tables/entities
2) if not, then check if adding attributes/columns to any of the existing tables/entties
3) if not, then add a new table

The decisions are never clearly cut and you should consider several scenarios and their consequences. You should also periodically review your model as it envolves to look for opportunities to normalize it as it will greatly impact overall functionality of your system.

So, for example, as you state that you already have a many to many table for groups the main question now is weather to reuse that table or create a new one.

This depends on the other attributes that are in this table. Maybe for your business model/use case it makes sense, maybe it does not.

Maybe you need to model a table called as "MemoRecipients" which will store info on recipients (delivery, acknowledgement, etc...). Then you should ask yourself do you need the times for all these statuses, etc, etc...

Only you can determine what is a proper design decision for your problem space.

Answer 2

It seems as if you need an inheritance heirarchy to achieve what you describe. Consider the diagram below:

In this diagram you create a parent table (I call it Poster) and the employee and group tables are children. You have to give the employee table and the group table the same primary key, that is, there are one to one links from group to poster and employee to poster.

This way, the poster_id field of memo is pointing to the super class. You can perform a join between poster, group and employee to determine whether you are refering to an employee or a group.

I left out the relationship between employee and group from the diagram as you said you have that already.

Note that this is just an illustration of what is possible, you solution may vary.