Search code examples
databasedatabase-normalization

Normalization of a database schema


Here are the relationships between 3 objects - A, B and C.
A:B - 1:M
A:C - 1:M
B:C - M:M, with the restriction that they must share the same A instance.

My current schema is as follow.
a (id, data)
b (id, a_id, data)
c (id, c_id, data)
b2c (b_id, c_id)

How to design a better schema to avoid data inconsistency?

I know the title of this post is kind of general. If any of you can think of a better title, fell free to edit this post.


As an example, I am going to develop an app for generating an ad wall. An ad wall is divided into many sections. Each section has a dimension (width and height). There are many ads, each of them also has a dimension. Think of an ad can show on multiple sections, and a section can has multiple ads rotating. So the relationship between sections and ads is many-to-many, but with the restriction that they must has the same dimension.


Solution

  • I think this is a business rule that should be enforced in code. Section:Ad as M:M is already normalized.

    However, I'm not sure you need a Dimension table, if that's what you're thinking as the 'A' table in your example.