Many-to-many relationship for many types of entities

Question

I have four tables: Disks, Folders, Files and Links. It is four types of entities, and they can not be merged into one type (e.g. into one table) because they have different number and types of attributes. And there are relationships:

• Disks can contain Folders, Files and Links;
• Folders can contain Folders, Files and Links too;
• Files and Links can't contain anything.

In addition, all entities have order in which they are displayed (e.g. "user defined", not alphabetical or something else). It is a simplified example of the actual problem, in real there are much more entities and relationships are more complicated.

So, what is the proposed structure of tables?

Thanks everyone who answer the question

Answer 1

It's actualy one-to-many relationships as:

• A Disk has 0 to many Folder and a Folder belongs to exactly 1 Disk
• A Folder has 0 to many File and a File belongs to exactly 1 Folder
• A Folder has 0 to many Link and a Link belongs to exactly 1 Folder

Disk Table

-- Disk
ID
Label
OrderNumber

Item Table representing all folders, files and links.

-- Item
ID
Label
ItemType COMMENT 'Folder|File|Link'
ParentId
OrderNumber

It is up to your business logic to not to have:

• File belongs to File
• Link belongs to File
• Folder belongs to Link

.. and so on

Answer 2

Use link tables

CREATE TABLE Disk_Files 
(
    DiskID int
   ,FileID int
   ,SortOrder int
)

CREATE TABLE Disk_Folders 
(
    DiskID int
   ,FolderID int
   ,SortOrder int
)

CREATE TABLE Disk_Links 
(
    DiskID int
   ,LinkID int
   ,SortOrder int
)

CREATE TABLE Folder_Files
(
    FolderID int
   ,FileID int
   ,SortOrder int
)

CREATE TABLE Folder_Links
(
    FolderID int
   ,LinkID int
   ,SortOrder int
)

In all link tables, SortOrder defines the ordinal position of the linked item within the relationship.

Answer 3

I would go with a table that defines the Rules which allows links to be made, and the display order for these.

DECLARE @Entity_Linkk_Rules TABLE(
        EntityFromType VARCHAR, --eg Disk
        EntitytoType VARCHAR, --eg Folder
        DisplayOrder INT
)

And then have a Many-To-Many link structure that include these Types

DECLARE @Entity_Links TABLE(
        EntityFromType VARCHAR, 
        EntityFromID INT,
        EntityToType VARCHAR,
        EntityToID INT
)

Something like that.

This would also easily allow you to expand the rules/links as required.

Answer 4

I'd go for the adjacency list model with additional checking for the references:

CREATE TABLE inode (type INT NOT NULL, id INT NOT NULL, parent INT NOT NULL, order INT NOT NULL, PRIMARY KEY (type, id), CHECK (type IN (1, 2, 3, 4)))

CREATE TABLE disk (type INT NOT NULL, id INT NOT NULL PRIMARY KEY, disk_attributes ..., CHECK (type = 1), FOREIGN KEY (type, id) REFERENCES inode (type, id))

CREATE TABLE file (type INT NOT NULL, id INT NOT NULL PRIMARY KEY, file_attributes ..., CHECK (type = 2), FOREIGN KEY (type, id) REFERENCES inode (type, id))

CREATE TABLE link (type INT NOT NULL, id INT NOT NULL PRIMARY KEY, link_attributes ..., CHECK (type = 3), FOREIGN KEY (type, id) REFERENCES inode (type, id))

CREATE TABLE folder (type INT NOT NULL, id INT NOT NULL PRIMARY KEY, folder_attributes ..., CHECK (type = 4), FOREIGN KEY (type, id) REFERENCES inode (type, id))

You'll need to implement additional checking will stored procedures or triggers.

This way, you'll be able to build hierarchies (like, find all subfolders of a disk) much more easily.