大数据库架构
我最近想解决这个问题，但我真的不知道如何解决。 我有一个应用程序，允许用户注册和创建配置文件（任意数量）。 对于每个个人资料，他们可以创建活动（每个个人资料需要多少个活动），并且对于每个活动，他们可以添加链接（链接数量有限，但无论如何都很大）。每个链接可以有自己的关键字（超过 1 个关键字）。
我想到的显而易见的事情是为用户准备一张表格，一张用于个人资料，一张用于活动，一张用于链接，一张用于关键字。但想想这一点，有些用户可能会使用相同的关键字，我不想在数据库中重复该信息 n 次。我不知道这在 mysql 中是否可行，但我希望在链接表中有一个字段，该字段将引用关键字表中关键字的 id。类似于 id 数组之类的东西。我希望这个实现是灵活的，允许我轻松检索关键字，更新“关键字数组”并执行某些计算（例如计算关键字的数量）。您能推荐一个可能的解决方案来实现这一点吗？
再次声明：我正在使用 mySQL 和 php。 谢谢。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
根据该描述，我想到了这些表格：
from that description i thought of these tables:
您应该创建一个表来存储关键字，即
id (int)keyword (varchar)并存储链接的关联表 ->关键字即
link_id (int)keyword_id (int)希望这有帮助！
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!
我认为您对每个实体的表的最初实现是正确的。如果您将关键字存储在单独的表中并将它们与 link_id 或类似的内容相关联，那么您可以比包含每个链接的所有关键字的数组更快地查找具有常见关键字的链接。
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.
我认为，尽管用户可能为不同的链接选择相同的关键字。这并不使它们在语义上相同。
如果有一个针对浮木和浮物的活动，并在链接上使用“贝壳”作为关键字，那么这与我在 unix 实用程序活动中用作关键字的“贝壳”不同。
坚持你最初的清晰和逻辑的模式，不要通过解决想象的问题来使其复杂化。
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.
你需要有一个多对多的表来存储链接的ID、用户的ID和关键字的ID（我假设所有链接都有关键字）
然后你就可以通过以下方式完成你正在谈论的事情正常的数据库操作。
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.
有2个想法
1）让每个用户都有自己的一组关键词
有一个用户表，并且在另一个表中有关键字，其中 userID 作为 FK。
当用户（无论其处于什么配置文件/营销活动）需要添加链接时，您会显示该用户的关键字。
该链接仍将通过包含keywordID和LinkID的连接表链接到keywordID
2) 全局关键词
有关键字只有keywordID和keyword
会有一个连接表来保存keywordID和LinkID，允许一个链接有多个关键字。
然后，前端必须确保用户在添加新关键字之前搜索现有关键字，这将有助于防止重复出现。添加关键字的过程还应该在添加之前检查现有值
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