CODEWITHSUNDEEP
sqlmysqldatabasedatabase-designdatabase-schema
user253530
user253530

Reputation: 2591

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.

Upvotes: 0

Views: 310

Answers (6)

James Anderson
James Anderson

Reputation: 27478

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.

Upvotes: 1

Amjid Qureshi
Amjid Qureshi

Reputation: 602

There are 2 ideas 1) have each user have their own set of keywords have a user table and have keywords in another table with userID as a FK.

When a user no matter what profile/campaign they are in needs to add a link you display the keywords for that user.

The link would still link to a keywordID via a join table that would hold keywordID and LinkID

2) global keywords have keywords just have keywordID and keyword there would be a join table to hold keywordID and LinkID, allowing a link to have multiple keywords.

The front end would then have to be made to ensure the users search existing keywords before adding new ones, this would help prevent double ups. The process that adds a keyword should also check for an existing value before adding

Upvotes: 0

Matt Phillips
Matt Phillips

Reputation: 11519

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.

Upvotes: 1

Dan D.
Dan D.

Reputation: 74655

from that description i thought of these tables:

user (id, ...)
campaigns (id, user_id, ...)
links (id, campaign_id, link)
keywords (link_id, keyword)

Upvotes: 2

Cfreak
Cfreak

Reputation: 19309

You need to have a many to many table which stores the ID of the link, the id of the user and the id of the keyword (I'm assuming all links have keywords)

Then you can accomplish what you're talking about just through normal database operations.

Upvotes: 0

Christian Joudrey
Christian Joudrey

Reputation: 3461

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!

  • Christian

Upvotes: 1

Related Questions