Mysql – An entitiy with at least one weak entity

database-designMySQL

I have the following design:

There is a list of parents in my database. A parent should have at least one child, and a child must belong to a parent (forget about the husband/wife relations).
(a child is a weak entity)

(No need to add rules for deletion/ update)

How can I achieve this?

I write SQL but I have the problems:

Create Table Parent(
  parentName Varchar(255),
  -- Should a parent have an entity named child?
  -- But a parent can have more than one child
  PRIMARY KEY(parentName)
);

Create Table Child(
 parentName varchar(255),
 name varchar(255),
 PRIMARY KEY(parentName, name),
 FOREIGN KEY (parentName) References Parent(parentName));

I'm assuming that I have the following error:

A parent should not exist without a child. However, I could not manage to maintain this.

As an example

parent 1, child1

parent 1, child2

parent 2, child2 –Not allowed!

parent 3, null — Not allowed!

null, child 4 — Not allowed!

Also, I need a child for other relations as well. So, in my opinion, combining child entity with a relation in a table is not a good solution for me.

Best Answer

As was mentioned in the comments, there could be name duplicates. It's better to have an id column in each table.

Second, adding NOT NULL constraint on the foreign key will enforce the rule that each child can have only one parent

Create Table Parent(
  id int NOT NULL UNIQUE,
  parentName Varchar(255),
  -- Should a parent have an entity named child? -- No.
  -- But a parent can have more than one child
  PRIMARY KEY(id)
);

Create Table Child(
 id int NOT NULL UNIQUE,
 parent_id int NOT NULL, -- NOT NULL - each child has a parent
 name varchar(255),
 PRIMARY KEY(id),
 FOREIGN KEY (parent_id) References Parent(id)
);

There's only one parent_id field, so each child has no more than one parent

Because each child can have only one parent (one-to-many) relationship, the two-table solution works.

When you have many-to-many relationship, you need a relationship table that contains foreign keys to both tables that represent entities in this relationship.

You might also want to add another UNIQUE to (parent_id, name) combination, to prevent one parent have multiple children with the same name. (Though, I have heard that some celebrity named all his sons "George")

You can't really enforce that each parent has at least one child, because then you wouldn't be able to add new parents to your database. Current process is:

insert a parent
insert that parent's children

You can't insert children first, because the constraints will prevent the insert of a child without a parent already existing.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

SQL Server – How to Create One-to-Many Relationship with Privileged Child

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB.

@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL 
    FOREIGN KEY (ParentID, ChildID) 
        REFERENCES Child (ParentID, ChildID)

In SQL-Server (that you are using), you also have the option of the IsFavorite bit column you mention. The unique favourite child per parent can be accomplished via a filtered Unique Index:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.

Read a quite old article: SQL By Design: The Circular Reference

When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?

In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as NOT NULL but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.

Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

SQL Server – How to Create One-to-Many Relationship with Privileged Child

Related Question