CODEWITHSUNDEEP
mysqldatabasedatabase-designsoftware-design
Barret Wallace
Barret Wallace

Reputation: 155

Order of elements referenced by Foreign Keys

I‘m thinking about a good DB design for this use case:

Now I want to maintain the order of articles for each topic. In which Table should I store this Information: topics, articles or a third one?

Any help is greatly appreciated!

Upvotes: 0

Views: 372

Answers (2)

Nic3500
Nic3500

Reputation: 8621

If the number of articles - topics links are variable, you need a link table.

So:

Table Articles
    idArticles   PK, int, auto-increment, not null
    Title        varchar, not null

Table Topics
    idTopics     PK, int, auto-increment, not null
    Title        varchar, not null

Table Topics-have-Articles
    idTopics     PK, FK to Topics:idTopics, not null
    idArticles   PK, FK to Articles:idArticles, not null
    Order        int, not null

Notes:

  • PK: primary key
  • FK: foreing key
  • In Topics-have-Articles, the PK is (idTopics,idArticles)
  • You say you want to maintain order of articles inside a topic. At first, I had put a date value. Based on your comment I modified it to an int value (Order). You can then setup an application page to take care of this ordering.
  • If you stay with a 1 to 1 relationship for the time being, this will still work, but allows you to easily transition to n to 1, or n to m relations.

Potential problems with the order field:

  • lets say you order your articles 1,2,3,4,5. What happens if you want to put one between 4 and 5? You have to reorder them all.
  • lets say you order 10,20,30,40. Then you have 10 "slots" to put new articles in between, but you will run out eventually.
  • this might not be a problem if your application is small. But you might have to write some code to reorder your articles on insert.
  • That might warrant another search in SO, like this one: how to maintain display order field in php

Upvotes: 1

nacho
nacho

Reputation: 5397

So if in the future you want to have many topics for one article and, i supose that one topic may have many articles, you will need a third table. If you are going to have only one topic for an article, you should insert it in the table article, one column for topic_id.

This will be a many-to-many relation so you need a third table with the id of the topic and the and the id of the article. The primary key will be (topic_id,article_id) so you only have one combination of article-topic. In this table both, topic_id and article_id are foreign keys.

Upvotes: 1

Related Questions