database schema design for grouping entities

Question

I'm trying to refactor some parts of a legacy database schema and am having trouble with coming up with the correct design.

The entities in question are:

samples, papers, studies

papers are associated with many samples
studies are associated with many samples

papers and studies have their own attributes not compatible with each other

samples can be associated with multiple papers and multiple studies

However, this separates out the grouping of papers and studies.

Here's how it looks:

An alternative I thought of was since both papers and studies are just grouping the samples together, I can combine these as one, and have FK from the group into their respective paper/study table.

Here's how it looks:

I'd like to know if the designs look reasonable and if there are any tradeoffs between the two different designs? Also are there alternatives to modelling the relations?

Answer 1

I think the first design is a right one. There are two M:M relations, Paper - Sample and Study - Sample. They are different by domain logic, so there is no sense to combine them in one relation and introduce extra entities for that purpose. First schema is a good normalized one. What is your goal? What problems do you try to resolve?

the schema doesn't have explicit grouping ...

OK, if you do require Group as a separate entity, your design could look like this:

The problem is, Group entity is weak. It is hard to propose any attribute to this entity except for ID. It is not handy to work with this scheme thought. When user edits paper's group, you have to choose, how to handle this situation. Should all other papers\studies 'see' this change too, or you have to create\search edited group and assign it to paper. I think it is wrong way to take if there is no additional business logic related to groups. Usually, when weak entities appear in a design, it means that set of abstractions has been chosen not properly. At the moment, I don't see how to justify Group entity.