Hello guys am workong on one social media network. Web site have some facebook simaliraty like (friends, wall and post). I want to create good db table design about post. Post like facebook have (text, attachments).
I have problem with organizing database table for user post's. I already create database model but that model is not realy good bcs i can only save text.
I want to add new field for attachment when user want add photo or video in post. What is te best approach for attahments. Create new table witch will hold all attachments and primary key with post ID is this okay?
My current psot table with only text looks like this: example
post_id account_id post_text date_created host_address
------- ---------- --------- ------------ --------------
1 1 Post text. 01.01.2015 01.01.2015
Now to have attachments in post, my plain is to edit this table like this:
post_id account_id attachmentid post_text date_created host_address
------- ---------- ------------- --------- ------------ --------------
1 1 1 Post text. 01.01.2015 01.01.2015
And finaly create new table that hold all attachments from posts:
atch_id post_id atch_name
------- ------- -----------
1 1 image1.jpg
2 1 image2.png
3 3 video.mp4
4 10 youtube-video
Is this good relation? Currently i have about 13.000 registred account and about 1000 post per day. So i need good relation for handling big data.
If you have better solution i would be grateful.
You don't need attachmentid in your post model. It's a one to many relationship. And, yes, this is the way it should be done.