Parent-Child Model

Question

Hello,

I was doing some research on modeling parent-children relationships in SQL and was unable to find anything particularly illuminating. Basically, I am curious what the most effective way to model something like this would be. For instance, how would you model a labeling system in SQL where:

1.) Every Label has a name, a VARCHAR(32)

2.) Every Label has a Parent

3.) A Parent can be another Label or a User (identified by a VARCHAR(32))

4.) The same Label name can be used provided another Label with the exact same Parent doesn't exist

It seems like modeling this would be easy if not for the constraint in 4. But if I add that constraint, it becomes more difficult for me to come up with a particularly compelling implementation of the model in SQL. If you see something that makes sense or can point me toward some literature, I would greatly appreciate it.

Answer 1

1. Every Label has a name, a VARCHAR(32)
2. Every Label has a Parent
3. A Parent can be another Label or a User (identified by a VARCHAR(32))
4. The same Label name can be used provided another Label with the exact same Parent doesn't exist

CREATE TABLE Labels
(patent_name VARCHAR(32) NOT NULL,
 parent_type CHAR(1) NOT NULL
   CHECK(parent_type IN ('L', 'U')),
 label_name VARCHAR(32) NOT NULL,
 PRIMARY KEY (patent_name, label_name));

Did I miss something? 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 2

Check out the following:

CREATE TABLE Label (
	LabelID INT IDENTITY(1,1) PRIMARY KEY,
	Name varchar(32) NOT NULL,
	ParentID INT REFERENCES Label (LabelID),
	UNIQUE (Name, ParentID),
	CreatedDate DATE default (CURRENT_TIMESTAMP));
GO
-- TRUNCATE TABLE Label;
INSERT Label(Name, ParentID) VALUES ('Category', NULL);
DECLARE @Parent INT = SCOPE_IDENTITY();

INSERT Label(Name, ParentID) VALUES 
('Bike', @Parent), ('Motorbike', @Parent), ('Automobile', @Parent),('Airplane', @Parent);

INSERT Label(Name, ParentID) VALUES 
('Bike',3);

INSERT Label(Name, ParentID) VALUES 
('Bike',1);
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__Label__9E4611E6F4E362CF'. Cannot insert duplicate key in object 'dbo.Label'. The duplicate key value is (Bike, 1).
The statement has been terminated.
*/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 3

1. Every Label has a name, a VARCHAR(32)
2. Every Label has a Parent
3. A Parent can be another Label or a User (identified by a VARCHAR(32))
4. The same Label name can be used provided another Label with the exact same Parent doesn't exist

CREATE TABLE Labels
(patent_name VARCHAR(32) NOT NULL,
 parent_type CHAR(1) NOT NULL
   CHECK(parent_type IN ('L', 'U')),
 label_name VARCHAR(32) NOT NULL,
 PRIMARY KEY (patent_name, label_name));

Did I miss something? 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Thank you for your input.

I should clarify point 4 as I phrased it incorrectly:

4.) The same Label name can be re-used provided it has a different parent than every other Label that has the same name (i.e. Label 'Good' with Parent User 'Celko' and Label 'Good' with Parent label 'GoodLabel' is legal).

I had a few questions about your proposed solution:

1.) Is it considered good SQL design to have a parent type field?

a.) Do fields like parent type lead to messier joins?

b.)What happens if at some point in the future Users are no longer identified by VARCHARs but by UNIQUEIDENTIFER. Does this approach work any more (it seems like the PRIMARY KEY becomes an issue in that case)

2.) Would it make sense to store the label names (the actual VARCHARs) in a separate lookup table? If so, what is the best way to deal with the double Foreign Key cycle that arises?

Thanks again.

Answer 4

Check out the following:

CREATE TABLE Label (
	LabelID INT IDENTITY(1,1) PRIMARY KEY,
	Name varchar(32) NOT NULL,
	ParentID INT REFERENCES Label (LabelID),
	UNIQUE (Name, ParentID),
	CreatedDate DATE default (CURRENT_TIMESTAMP));
GO
-- TRUNCATE TABLE Label;
INSERT Label(Name, ParentID) VALUES ('Category', NULL);
DECLARE @Parent INT = SCOPE_IDENTITY();

INSERT Label(Name, ParentID) VALUES 
('Bike', @Parent), ('Motorbike', @Parent), ('Automobile', @Parent),('Airplane', @Parent);

INSERT Label(Name, ParentID) VALUES 
('Bike',3);

INSERT Label(Name, ParentID) VALUES 
('Bike',1);
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__Label__9E4611E6F4E362CF'. Cannot insert duplicate key in object 'dbo.Label'. The duplicate key value is (Bike, 1).
The statement has been terminated.
*/

---

Kalman Toth SQL 2008 GRAND SLAM
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Thank you again for your input Kalman.

Your solution seems to model a labeling system where labels can either by sub-labels or top level labels. But it does not appear your proposed solution accounts for a label having two (or more) different types of parents (either a Label as a parent or a User as a parent) unless there is something I am missing. Is there a recommended model for a situation where Labels can have different types of parents with different data types for natural keys (i.e. Label with a VARCHAR(32) Label parent or a UNIQUEIDENTIFIER User parent)?

I appreciate your input.

Answer 5

A pair of exclusive FK to different tables can be as follows

CREATE TABLE [User] (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name varchar(32) NOT NULL UNIQUE
);
CREATE TABLE [Label] (
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name varchar(32) NOT NULL,
	LParentID INT REFERENCES [Label] (ID),
	UParentID INT REFERENCES [User] (ID), 
	UNIQUE (Name, LParentID, UParentID),
	CHECK (LParentID is null or UParentID is null)
);
-- top level
insert [User] (Name) values ('Anton'),('Mery') ;
insert [Label] (Name) values ('TopLabel');
-- childs OK
insert [Label] (Name,LParentID) values ('label2', 1)
insert [Label] (Name,UParentID) values ('label2', 1)
insert [Label] (Name,UParentID) values ('label3', 1)
-- now fails
insert [Label] (Name,LParentID) values ('label2', 1)
insert [Label] (Name,UParentID) values ('label2', 1)
-- fails
insert [Label] (Name,LParentID,UParentID) values ('label4', 1,1)

"The same Label name can be used provided another Label with the exact same Parent doesn't exist"+ "The same Label name can be re-used provided it has a different parent than every other Label that has the same name " is uniqeness of pair (parent, child). Which becomes uniqeness of (Name,LParentID,UParentID) in above case.

---

Serg

Answer 6

Thank you for your input SergNL. I had a few questions:

1.) Is it considered good SQL design to have the multiple reference fields

a.) Are there any complications that arise when joining with the model?

b.) Is the model considered scalable if you go from 2 types of parents to many different types of parents?

Thank you.

Answer 7

1) Multiple reference fields  is  quite usual pattern.

a) I see no problems with joins. If you mean tracking hierarchy recursive CTE is powerfull enough, but resorce consuming sometimes.

b) When tables are proliferating too fast you may wish to implement a kind of generic model, see http://www.essentialstrategies.com/publications/modeling/advanceddm.htm

for example. Many more may be found on the net.

---

Serg