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 th开发者_C百科e 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.

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.

You're already defining the B:C relationship with these two relationships: A:B - 1:M A:C - 1:M

I don't see why you need a B:C relationship table.

Given your example, I only see two tables. Ads have a width and height, but there's a 1:1 relationship, so width and height are just fields in the ads table. Sections also have a 1:1 relationship to width and height. Only two tables. I don't see a strict relationship between ads and sections, either.