One-to-many, parent-child tables circular relationshp in MySQL

I'm facing this problem:

I have a parent table, and a child table, one parent can have multiple children, standard story.

These are the constraints:

• each parent must have at least one child,
• each parent must have one favourite child,
• each parent can have one least-favourite child

How to desing this in SQL?

I'm not sure the standard parent-child tables can be used because of the circular relationship:

Parent table:
parentId
favouriteChildId NOT NULL
least开发者_JS百科FavouriteChildId NULL

Child table:
childId
parentId

I was thinking of using a bridge table, but am not sure how to model these constraints.

EDIT: Just to add some clarity, here is part of the problem context:

There is the Price table (child), and PriceGroup table (parent).

PriceGroup has multiple prices, one mandatory mainPrice (favouriteChild) and can have one officialPrice (leastFavouriteChild).

The following is not related to the problem, but sheds some light on the context: Prices are grouped according to Products they refer to, and one Product can have multiple prices - these are then grouped in price groups, and each group needs reference to a main price, and an official price (if there is one).

Out of the business rules that you have given

1. each parent must have at least one child,
2. each parent must have one favourite child,
3. each parent can have one least-favourite child

Your solution of

Parent table:
parentId (PK)
favouriteChildId NOT NULL (FK)
leastFavouriteChildId NULL (FK)

Child table:
childId (PK)
parentId (FK)

satisfies 2 and 3. But also it satisfies the 1 (since favouriteChildId NOT NULL will not allow creating parent records with no children).

Since you already have the above, I will assume that the real question for you is how to make parentId in Child table NOT NULL.

Normally, there are provision in SQL so that you could do something like

BEGIN TRANS
INSERT INTO TABLE1 (FK not checked yet)
INSERT INTO TABLE2 (FK not checked yet)
COMMIT (All integrity checked)

in that case 'circular reference' would not be a problem (see DEFERRED)

Mysql does not support it, so you have following options

Triggers:
It can be assumed that at the time of inserting parent record a favorite child is already known then you can have a trigger that would run before insert on the parent table and

• insert the favorite child into child table getting its ID
• insert the parent record with the child's ID

NOTE: Problem is that this way you can formally satisfy the criteria, but to insert the child record first you will have to either use additional columns in parent so that the trigger can know about other fields in child table or insert a blank record (in either case the design is not clean)

Integrity through security
The above can be implemented as stored procedure without requiring additional fields on Parent table level. However stored procedures could, normally be bypassed so it does not qualify as real integrity rule.

There is, a generic way to make something achieved with a stored procedure qualify as integrity rule - and that is to remove write permissions for all regular users (and applications) for these tables and allow the data to be changed only through stored procedure.

EDIT: Regarding triggers there is also a way to implement the rule with triggers, and that is to accept that you will have to insert records separately and that you can must have, at one moment data that breaks your business rules.

In that case you can have a STATUS atrribute for the parent record (for example: COMPLETE vs INCOMPLETE) and make favouriteChildId a NULLable FK, but when updating the status to COMPLETE you can have trigger check that integrity is respected.

This requires additional column but can make things quite clean (you can actually create a view on this table that would only expose only records that are COMPLETE, effectively making it look like the table with FK NOT NULL).

You can model (to a degree) the other constraints:

Parent Table
parentId (PK)

Child table:
childId  (PK)
parentId (FK)

Is Favorite table:
childID (PK)(FK)

Is Least Favourite table:
ChildID (PK)(FK)

A row would always be inserted into Is Favorite Child; one would be inserted in Is Least Favourite only if there is a least favorite child: Insertion, update, deletion done by a trigger on a view; selection by a Left Join on the tables.

This does not deal with the mandatory nature of the Favourite Child relation - which would have to be dealt with by the insert/update/delete triggers.

If just one level deep:

Parents ( ParentID, Title, etc )
Children ( ChildID, ParentID, Title, etc )

Every Child must always have exactly 1 Parent and Parents always have >= 0 Children. (There's no way around that.)

If multiple (unknown) levels deep:

Items ( ItemID, ParentItemID NULL, Title, etc )

Very simple: Items.ParentItemID = Items.ItemID

edit
If you require several (unknown) levels deep, the queries will be multiple and caching the total result would be a very, very good idea. (Every child would have another query to fetch it's direct children etc etc.)