How to structure a database schema to allow for th

Question

Among all the tables in my database, I have two which currently have a Many-to-Many join. However, the actual data population being captured nearly always has a One-to-Many association.

Considering that I want database look-ups (doctrine queries) to be as unencumbered as possible, should I instead:

• Create two associations between the tables (where the second is only populated in these exceptional cases)?
• Change the datatype for the association (eg to a text/tinyblob) to record a mini array of the 2 (or technically even 3) associated records?

This is what I currently have (although TableB-> JoinTable is usually just one-to-one):

TableA.id --< a_id.JoinTable.b_id >-- TableB.id

So, I am looking to see if I can capture the 'exceptions'. Is the below the correct way to go about it?

TableA.id     TableB.id
       +----< TableB.A_id1
       +----- TableB.A_id2
       +----- TableB.A_id3

Answer 1

You seem to be interested in:

-- a and b are related by the association of interest
Foo(a, b)

-- foo(a, b) but not foo(a2, b) for some a2 <> a
Boring(a, b)
unique(b)
FK (a, b) references Foo

-- foo(a, b) and foo(a2, b) for some a2 <> a
Rare(a, b)
FK (a, b) references foo

If you want queries to be unencumbered, just define Foo. You can query it for Rare.

Rare = select * from Foo f join Foo f2
    where f.a <> f2.a and f.b = f2.b

Any other design suffers from update complexity in keeping the database consistent.

You have some fuzzy concern about Rare being much smaller than Foo. But what is your requirement re only n in a million Foo records being many:many by which you would choose some other design?

The next level of complexity is to have Foo and Rare. Updates have to keep the above equation true.

It seems extremely unlikely that there is a benefit in reducing the 2-or-3-in-a-million redundancy of Foo + Rare by only having Boring + Rare and reconstructing Foo from them. But it may be of benefit to define a unique index (b) for Boring which will maintain that a b in it has only one a. When you need Foo:

Foo = select * from Boring union select * from Rare

But your updates must maintain that

not exists (select * from Boring b join Rare r where b.b = r.b)

Answer 2

Change the datatype for the association (eg to a text/tinyblob) ?

Please don't do that. If you do the people maintaining your database will curse your name unto the thousandth generation. No joke.

Your best bet here is to rig a one-to-many association. Let's say your table a has an integer primary key a_id.

Then, put that a_id as a foreign key column in your second table b.

You can retrieve your information as follows. This will always give you one row in your result set for each row in a.

 SELECT a.this, a.that, GROUP_CONCAT(b.value) value
   FROM a
   LEFT JOIN b ON a.a_id = b.a_id
  GROUP BY a.this, a.that

If you don't mind the extra row for your one-in-a-million case it's even easier.

 SELECT a.this, a.that, b.value
   FROM a
   LEFT JOIN b ON a.a_id = b.a_id

The LEFT JOIN operation allows for the case where your a row has no corresponding b row.

Put an index on b.a_id.