Table relationship design help

[Hikaru12]

I'm just starting to develop relationships in databases. Normally, I use simple one table database designs for very small scripts or I forgo SQL for NOSQL databases like MongoDB all together. 

 

My idea is simple: I want to have a table of filenames (which correspond to actual files within a directory) and I want to relate that to project names. So for example, if I'm looking to download a bunch of files belonging to a particular project I want to just be able to select the projects name and get that info. Right now, I have it spreadsheet style where every time I want to associate a file to a project I have the user type in the project name which opens itself up to a whole host of issues, including misspelling the name and adding it to something entirely new.  I plan on changing this to a prepopulated selectbox to guide the user in choosing the right one.

 

Anyway, I'm not sure how to define this relationship as I can have several files that can belong to either one project or a bunch of different projects. It's up to the user to decide. Would this be a many to many relationship? How would I define this in SQL?

 

If it's a one to many then I could imagine relating the Project Name field in the projects table to the Project Name field in the filenames table. 

 

Thanks in advance for the help.

[Ciccioo]

many to many relationships in rdbms are represented using an intermediate table, like this.

 

Project (ID, name, ...)

File(ID, path, ...)

FilesInProject(projectID, fileID)

[Hikaru12]

many to many relationships in rdbms are represented using an intermediate table, like this.

 

Project (ID, name, ...)

File(ID, path, ...)

FilesInProject(projectID, fileID)

 

Here's what I have:

 

A table called 'myversions' with the following file and version info:

• id Primary
• filename
• filetype
• vernum
• comments
• created

The other table is called 'projects' with:

• id
• name

Here's the query to join the two but it's not working. Says cannot add the foreign key.

CREATE TABLE filesinproject (id INT PRIMARY KEY AUTO_INCREMENT,fileid INT NOT NULL,projectid INT NOT NULL,FOREIGN KEY (fileid) REFERENCES myversions(id) ON DELETE NO ACTION,FOREIGN KEY (projectid) REFERENCES projects(id) ON DELETE NO ACTION)

[Ciccioo]

Says cannot add the foreign key.

is projects.id indexed? what dbms/storage engine are you using?

also note that filesinproject doesn't need to have an id. the primary key should be (fileid, projectid), that will also avoid duplicate file records for the same project

[Hikaru12]

is projects.id indexed? what dbms/storage engine are you using?

also note that filesinproject doesn't need to have an id. the primary key should be (fileid, projectid), that will also avoid duplicate file records for the same project

 

Yes, it's indexed and listed as a primary key. Just a typo above. Mysql and MyISAM. I'm using SQLWave as my DBMS. I'll try phpmyadmin. 

 

EDIT: All set. Didn't specify the storage engine within the query and for whatever reason that was why it couldn't set up the relationship.

[Ciccioo]

Yes, it's indexed and listed as a primary key. Just a typo above. Mysql and MyISAM. I'm using SQLWave as my DBMS. I'll try phpmyadmin. 

 

EDIT: All set. Didn't specify the storage engine within the query and for whatever reason that was why it couldn't set up the relationship.

that's weird, myisam doesn't support foreign key constraints, i think you're using innodb. well done anyway

[Hikaru12]

that's weird, myisam doesn't support foreign key constraints, i think you're using innodb. well done anyway

 

Just double checked you're correct. It is INNODB. Ran a Show Engines query just to make sure.

 

EDIT: Doesn't work with INNODB. Run into same issue. Keeps returning: "ER_CANNOT_ADD_FOREIGN (1215) - Cannot add foreign key constrain"

[Ciccioo]

Just double checked you're correct. It is INNODB. Ran a Show Engines query just to make sure.

 

EDIT: Doesn't work with INNODB. Run into same issue. Keeps returning: "ER_CANNOT_ADD_FOREIGN (1215) - Cannot add foreign key constrain"

can you post the CREATE TABLE statements for your myversions and projects tables?

[Hikaru12]

can you post the CREATE TABLE statements for your myversions and projects tables?

CREATE SCHEMA `versioncontrol` COLLATE latin1_swedish_ci;USE `versioncontrol`;CREATE TABLE `myversions`(  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `filename` VARCHAR(100) NOT NULL,  `filetype` VARCHAR(20) NOT NULL,  `vernum` VARCHAR(6) NOT NULL,  `comments` VARCHAR(500) DEFAULT NULL,  `created` DATETIME DEFAULT NULL,  PRIMARY KEY (`id`))ENGINE = InnoDBAUTO_INCREMENT = 41COLLATE = latin1_swedish_ciROW_FORMAT = COMPACT;CREATE TABLE `projects`(  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,  `projectname` VARCHAR(25) NOT NULL,  PRIMARY KEY (`id`))ENGINE = InnoDBAUTO_INCREMENT = 8COLLATE = latin1_swedish_ciROW_FORMAT = COMPACT;ALTER TABLE `myversions` DISABLE KEYS;INSERT INTO `myversions` VALUES    (38, 'test_Ver 0.02.txt', 'text/plain', '0.02.', 'First minor.', '2015-06-23 09:45:05'),    (39, 'test_Ver 1.txt', 'text/plain', '1.', 'First major upload.', '2015-06-23 09:45:58'),    (40, 'test_Ver 1.01.txt', 'text/plain', '1.01.', 'First minor after major', '2015-06-23 09:46:31');ALTER TABLE `myversions` ENABLE KEYS;ALTER TABLE `projects` DISABLE KEYS;INSERT INTO `projects` VALUES    (6, 'Sample'),    (7, 'Bucket List');ALTER TABLE `projects` ENABLE KEYS;