PHP/MySQL relationship table advice PHP/MySQL relationship table advice codeigniter codeigniter

PHP/MySQL relationship table advice


Yes, there is a bit of a disadvantage. You could end up with multiple rows for the same setting, each might contradict each other. So, how you insert rows into this table, is very important.

If you will not add any more sections, might as well have the portfolio table:

CREATE TABLE `portfolio`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`content` TEXT NULL,`showHome` BOOLEAN NULL,`showClient` BOOLEAN NULL,`showMember` BOOLEAN NULL)

And then the table which links the users to their portfolios,

CREATE TABLE `portfolio_user`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`portfolio` INT NOT NULL,`user` INT NOT NULL)

If you are going to add more places where the portfolio can be displayed later, or if these places are dynamic, your method will work. I would just change 'type' to 'place' as that is easier to understand, then either use ENUM or another table to define the places that the portfolio can be shown.


Can there be one or many portfolio items for each location?

If many, use link tables, if only one use direct foreign key field to link to the portfolio.

I would advice against using one link table for all three based on personal experience with exactly such designs and the problems that can surface later.