I am creating a PHP / MySQL website and now I am working on my database design. I have a database and MySQL experience, but I have never structured the database from scratch for a real-world application that I hope will have good traffic, so I would like to hear advice from people who have already done this to avoid common mistakes . I hope my explanations are not too confusing.
What I need
In my application, the user should be able to write a message (title + text), and then create an “object” (which can be anything like a video or song, etc.) and attach it to after. The site has a list of predefined object types that the user can create, and I will have to add new types in the future. The user should also be able to see the details of the object on the highlighted page and add a comment to it - the same applies to messages.
What i tried
I created a table objectswith these fields: oid, type, nameand date. This table contains entries for anything that the user should be able to add comments (such as messages and objects). Then I created a table postmetathat contains additional message data (such as text, author, last edited date, etc.), a table videometafor data about the video object (URL, description, etc.), and so on. Table postobject( pid, oid) associates objects with messages. In addition, there is a table commentsthat contains the text of the comment, the author and the identifier of the object to which it relates.
, , ( ), , "meta" PHP .
, , , . objects "post" postmeta, . postobject, , , comments, .
? ? , , objects , ( , ). , , ? ?
: , , . , : posts, postmeta, postobject {$objecttype}meta ( users, , ). , ?
, NoSQL (MongoDB) ( ). -, , . : . , , JOIN? DBRef , ? MySQL JOIN, , ?
, - . , , , .