I'm building a little learn app. It bases on the principle of cards. You can learn vocables, graphs, formulas etc. So you have different types of content/cards. Basically, I divide them into 2 types:
Vocable Cards => The front and back of the card can have several words (synonyms). Also having auto translate functionality.
Common Cards => Can mix images, text and forumlas. Includes everythign else. From Biology to Physics. Special functionality is a formula generator and a paing/drawing tool.
At the moment, I'm having 2 questions.
What's the best Database Design for this and how to save such content? The content is cs, html etc.
The vocable cards are only words or let's say strings. If the user types in bigger strings, I'll disable the translate functionality. Also, some cards might share some synonyms, should I take care of this? I could create a vocabel list and some m:n relationship table or just save all the synonyms as a json/csv in one column in the cards table. The later approach would also fit for the second type of cards, the common ones. (Didn't find a good name for the myet)
The images will be stored localy.
I somehow don't get a nice solution. I need to somehow set the type of the card. And also I need to know if I can use the same design for each content type.
At the moment, I'd do this:
But still, somehow I'm not convinced. Maybe it's better to put al lthe cards stuff together and just save the content in the frontContent and backContent cplumn and determine it's type by a type column. Also, can I make the frontContent and backContent on the vocableCard an auto increasing number and make such an m:n relationship?
I'll use doctrine, an ORM, Symfony2 and thus PHP. The whole thing will be done in Psql or MySql, didn't decide yet. Any recommendations? It will also be developed for mobile platforms and thus run on liteSql. I'm also adding a synchronizing functionality.
I really apprechiate any help.