Edit Question - KnowledgeHub

Title Body

I need to design tables which stores all the metadata of files (i.e., file name, author, title, date created), and custom metadata (which has been added to files by users, e.g. CustUseBy, CustSendBy). The number of custom metadata fields cannot be set beforehand. Indeed, the only way of determining what and how many custom tags have been added on files is to examine what exists in the tables. To store this, I have created a base table (having all common metadata of files), an Attributes table (holding additional, optional attributes that may be set on files) and a FileAttributes table (which assigns a value to an attribute for a file). CREAT TABLE FileBase ( id VARCHAR(32) PRIMARY KEY, name VARCHAR(255) UNIQUE NOT NULL, title VARCHAR(255), author VARCHAR(255), created DATETIME NOT NULL, ) Engine=InnoDB; CREATE TABLE Attributes ( id VARCHAR(32) PRIMARY KEY, name VARCHAR(255) NOT NULL, type VARCHAR(255) NOT NULL ) Engine=InnoDB; CREATE TABLE FileAttributes ( sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, fileId VARCHAR(32) NOT NULL, attributeId VARCHAR(32) NOT NULL, attributeValue VARCHAR(255) NOT NULL, FOREIGN KEY fileId REFERENCES FileBase (id), FOREIGN KEY attributeId REFERENCES Attributes (id) ) Engine=InnoDB; Sample data: INSERT INTO FileBase (id, title, author, name, created) VALUES ('F001', 'Dox', 'vinay', 'story.dox', '2009/01/02 15:04:05'), ('F002', 'Excel', 'Ajay', 'data.xls', '2009/02/03 01:02:03'); INSERT INTO Attributes (id, name, type) VALUES ('A001', 'CustomeAttt1', 'Varchar(40)'), ('A002', 'CustomUseDate', 'Datetime'); INSERT INTO FileAttributes (fileId, attributeId, attributeValue) VALUES ('F001', 'A001', 'Akash'), ('F001', 'A002', '2009/03/02'); Now the problem is I want to show the data in a manner like this: FileId, Ti

Tags (comma-separated) Cancel