CODEWITHSUNDEEP
databasedatabase-design
sajid khan
sajid khan

Reputation: 99

Relationship issue in an ERD

I have created a simple ERD for a cultural site. Are these relationships correct?

cul_categories ( ||-----|<- ) cul_pages // Travel has at least one page

cul_categories ( ||-----O-<- ) sub_categories //e.g Arts---->Music, painting etc. ,or there can be, for example, a Travel which has no sub-category.

sub_categories ( ||-----|<- ) cul_pages // if we have Music then at least one page for it.

enter image description here

Upvotes: 2

Views: 532

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29649

Hard to be sure if it's correct, but there is a problem. You cannot enforce the rules about "pages" being in EITHER a category OR a subcategory, because you can't make the foreign key columns not-null.

To avoid this, you could change the model as follows:

TABLE: Category
-------------------
CategoryID (PK)
ParentCategoryID (FK)
Name

TABLE: Page
--------------
PageID (PK)
CategoryID (FK)
...

This allows you to create a NOT NULL foreign key relationship from Page to Category; categories with a NULL ParentCategoryID are "top level" categories, and categories with a parentCategoryID are subcategories.

Sample data:

Category

CategoryID    ParentCategory    Name
-----------------------------------------
1             null              Arts
2             1                 Painting
3             1                 Music
4             null              Travel


Page

PageID    CategoryID        Name
-------------------------------------
1         2                 Page about painting
2         3                 Page about music
3         3                 Another page about music
4         4                 Page about travel

Upvotes: 2

Related Questions