How to relate the data in two MySQL tables?
I am designing a web app which basically has to store two kinds of elements: posts and containers (which are arrays of posts)
I have done the database so that i store in each row of the container the stringified version of the array of the posts which it holds.
The issue is that when a post is deleted each time i call the container i have the id of the post and i have to check if the post is alive or not.
Is there a better way to do this ? for example structuring the container table to hold "pointers" to the actual posts ?
Thanks a lot!
Here goes the answer to back my comment that recieved some positive feedback :) although you already have answers up there.
Your Container is going to hold the posts. So it's like one basket holds many items in it. So for each post, there's a container id. For container table - container id is primary key. posts table - post id is primary key. To have the Foreign key relationship, you set your container id as foreign key in your posts table. If this is confusing - just see the table schema below. If you want to delete any posts related to a container when the container is deleted, you can use
ON CASCADE DELETE on the parent key (foreign key) to remove any orphans (childrens without parents). If you wish to have auto incremented ID, you can use the
AUTO_INCREMENT otherwise just remove it.
And please take a look sql syntax for further understanding as well.
CREATE TABLE tblParent ( `CID` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `field2` varchar(100), `field3` varchar(100) ); CREATE TABLE tblchild ( `PID` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `PCID` int NOT NULL, `field2` varchar(100), `field3` varchar(100), FOREIGN KEY REFERENCE tblParent ('CID') ON DELETE CASCADE );
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]