CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
dpi
dpi

Reputation: 204

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!

Upvotes: 0

Views: 151

Answers (5)

bonCodigo
bonCodigo

Reputation: 14361

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

Upvotes: 1

Makoto
Makoto

Reputation: 106508

Using a foreign key relationship between the two tables would be ideal.

First, if I understand the relationship between your data properly, your Container table would be the table that holds information relating to a unique id. The `Posts table then has its own unique id, a containerId, and the post content itself.

Drawn out, it'd look a little like this.

Container
---
id

Posts
---
id | containerId | post

The relationship between Container and Posts is presumed to be one-to-many; that is to say, one container may have many posts, but one post can't belong to many containers. If you wanted this relationship, you'd likely have to add another column for that to Posts.

Upvotes: 2

Barranka
Barranka

Reputation: 21067

If I understand correctly, you have a one-to-many relation: each container can have many posts, but each post is related to a single container. So your database design should reflect this.

Example:

create table tbl_containers (
    containerId int unsigned not null auto_increment primary key,
    containerDescription varchar(100)
);
create table tbl_posts (
    postId int unsigned not null auto_increment primary key,
    containerId int unsigned not null,
    postText varchar(100),
    index idxContInd(containerId)
);

When you fill your data, every post must be associated with a container; in other words, before a post can be entered there must be a container to hold it, and if there's no "suitable" container, it should be created before the creation of the post.

After your data is in the tables, it is easy to look for the posts, and delete them, independently of the container each post belongs to.

Hope this helps you

Upvotes: 2

Thomas Francois
Thomas Francois

Reputation: 1

You can create a third table with two fields, 'container_id' and 'post_id' for example. Then when you delete a post, you delete all entries containing the post_id, same thing for containers. Make sure that the table primary_key is composed by the two_fields to avoid entries duplication.

Upvotes: 0

Tiit
Tiit

Reputation: 520

I'm not sure if I understood you correctly but I would do this the other way around: You have your POST table, which has a field called container_ID. If the post gets deleted then a row in the POST table would get removed (including the pointer to its parent entity in the container_ID). In this case you can create a foreign key relations between the tables in order to keep the data consistent and relations intact within the database. Please clarify if I misunderstood something..

Upvotes: 0

Related Questions