TL;DR : what is the best way to store array-like information (that is, more than one information) in MySQL tables?
Newbie here.
I’m starting a site that’s pretty much a Wikipedia but, instead of articles, it has only listed informations (I don’t have the pretension of beating Wikipedia or whatever, it’s a personal (open source, ASAP) project that will help me filling information I read). Example:
Instead of “Socrates” page:
"Socrates.
Socrates was a philosopher from Ancient Greece that lived in Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua…"
One gets:
"Socrates.
- Lived in Athens 400BC (source)
- Philosopher from Ancient Greece (source)
- Teacher of Plato (source) etc.
An important difference to Wikipedia (amongst other differences) is that all listed infos must have a source and may be voted by other users.
To implement this idea, I wanted to use JS for user interaction (e.g., filtering or ordering information), PHP for server scripts and MySQL to store all data (infos, comments, users, etc.). Thinking about the voting aspect, I thought about creating a table in the database for each information (in the former Socrates example, “Lived in Athens 400BC”) that is voted up or down. The “votes” table would have these columns:
CREATE TABLE IF NOT EXISTS votes (
info_id INT UNSIGNED NOT NULL, -- the id of the information (e.g. "Lived in Athens 400BC", id number: 42) that is stored in another table called 'informations'
upvoted_users MEDIUMTEXT NOT NULL, -- a string with the ids (stored along with 'username' etc in another table called 'users') of all the users that upvoted the information
downvoted_users MEDIUMTEXT NOT NULL
) DEFAULT CHARSET UTF8;
The string for upvoted_users
ids would be something like: 'userid1, userid2, user white space id3, ...'
. This string would be parsed by PHP and, after calculating count(array(upvoted_users)) - count(array(downvoted_users))
, the number would be sent to display on the page the voting number of the information.
My question: Am I doing this the right way? Suppose that instead of up/down voting I wanted to store a 0-10 [int]
rating that the users could give, would storing rating_users
as a string (userId:rate): 'userid1:4, userid2:9, user white space id3:0, ...'
be good or is there a better way for doing it?
As an aside, where do I go to ask people to join my project? I thought about the section “Project Feedback” in this forum, but I don’t have the project ready yet.
Thanks a lot, and happy 2017!