schema for big database
I was trying to solve this issue recently, but i don't really know how. I have an application that allows users to register and create profiles(as many as they want). For every profile they can create campaigns(as many as they want per profile) and for each campaign they can add links(there is a limited number for the links but it's big anyway). Each link can have it's own keywords (more than 1 keyword).
The obvious thing that came to my mind was to have a table for users, one for profiles, one for campaigns, one for links and one for keywords. But think of this, some users may use the same keywords and i don't want to repeat that information over the database n times. I don't know if this is possible in mysql but i would like to have a field in the links table which will refer to the ids of the keywords in the keywords table. something like an array of ids. I would like this implementation to be flexible, allowing me to easily retrieve the keywords, update the "array of keywords" and perform certain computations (count the number of keywords for example). Can you recommend a possible solution on how to implement this?
Just to state again: I'm using mySQL and php. Thank you.
You should create a table to store the keywords i.e.
id (int) keyword (varchar)
And store an association table for links -> keywords i.e.
link_id (int) keyword_id (int)
Hope this helps!
from that description i thought of these tables:
user (id, ...) campaigns (id, user_id, ...) links (id, campaign_id, link) keywords (link_id, keyword)
I argue that your initial implementation of a table for each of the entities is correct. If you store keywords in a separate table and associate them with a link_id or something like that then you can look up links with common keywords much faster than an array containing all the keywords for each link.
I would argue that althought its possible that the same keyword may be choosen for different links by a user. This does not make them semantically the same.
If if have a campaign for driftwood and flotsom and use "shells" as a keyword on a link this is not the same "shells" as I would use as a keyword on the unix utilities campaign.
Stick with your original clean and logical schema and dont complicate it by solving imaginary problems.