database - Many-to-many relationship for many types of entities -
i have 4 tables: disks, folders, files , links. 4 types of entities, , can not merged 1 type (e.g. 1 table) because have different number , types of attributes. , there relationships:
- disks can contain folders, files , links;
- folders can contain folders, files , links too;
- files , links can't contain anything.
in addition, entities have order in displayed (e.g. "user defined", not alphabetical or else). simplified example of actual problem, in real there more entities , relationships more complicated.
so, proposed structure of tables?
thanks answer question
i'd go adjacency list model additional checking 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 implement additional checking stored procedures or triggers.
this way, you'll able build hierarchies (like, find subfolders of disk) more easily.
Comments
Post a Comment