How to relate the data in two MySQL tables?


Problem :

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!



Solution :

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
);

Mysql Tutorials

Mysql Howto..

How can I select rows which foreign key relation elements are ONLY past in time or don't exist at all

show best matches in a mysql tag system

How do I return a field in MySQL from a sub-query linked to the main query results?

How to get back the last inserted row mysql

MySQL - how can I avoid high load when I switch schemas (databases)?

How do I group a date field to get quarterly results in MySQL?

how to select a highest float value from mysql with active record class codeigniter

How to convert given number of days to years, months and days in MySQL?

How to be enable to use man command for mysql

How to migrate MySQL to MySQLI

how do I insert a row to a MySQL table in Python?

How to order database records based on these criteria?

Linux + MySQL: How to make outputs/responses visible?

How to avoid concurrent access to payment link in my website

How to query for the 'other' double quotes

How to display mysql error while using PDO in a connection class?

How to do group by chunk wise in SQL?

How to get single column table values from mysql db to dropdownbox using php ajax? [closed]

How to Add Multiple Rows in MySQL Php

How to access mysql database with socket.io

How can I select all rows in a table, and cross-check it with another table?

How to use a sequence table

How to match system date with the date from database mysql

Innodb - Running an INSERT INTO SELECT - do records not show up until the whole thing is done?

how to drop unique key on foreign key in mysql?

How to improve MySql query that trying to find distinct values from two tables?

how to Get the list of sites (domains) from MySQL database using PHP?

How do I display a part of column Value in sql?

MySQL Triggers: How to know which script called it?

How to unify three different queries in mysql when exists multiple criteria?