How to design this database table concept?

Question

Suppose I have a table Articles. An article can have only one topic attached to it. But it is also possible that article has no topic.

1. Should I make topic a new column in Articles table? OR

2. Should I create a table topics2articles and map together a topic with article? (In this case I do not have to add extra column for Articles table.

I don't like the 1st solution because then some rows will have NULL in them? Or is this reasonable to have NULL in this case?

What do you think?

Thanks, Boda Cydo.

Answer 1

I don't see any reason for you to make a new table just to avoid having rows with NULL in them.

It would make sense to add it to the article table, and probably make it easier to maintain since you wouldn't have to join against another table.

Answer 2

I would probably suggest that if topic has other related data that you should create 2 tables: one for ARTICLE and one for TOPIC with the ARTICLE PK as a foreign key in TOPIC.

Answer 3

One-to-one relations are generally just an annoyance... Keep them in a single table, as long as row length doesn't become a bottleneck of your application.

Answer 4

As you pointed out, in case of (1) you will have some NULL rows. In case of (2) you will have an extra table with 2 indexes. In my opinion, if there is no chance that article will ever have more than 1 topic and a number of articles without topics is not greater than 50-70% of the total number of articles (1) will work better.

Answer 5

This sounds like the kind of scenario that a pointy haired boss might use to inflict undue pain on you later when they ask that topic be used for tagging in a many-to-many fashion.

For that reason, I would go with #2.

If you're really confident that it won't turn around and bite you, #1 is a good choice. Avoiding NULLs seems like a pretty weak reason not to go with it. It's not clear if you were intending to store them as text or FK's to a topic table, but I'd go with a separate table for topics.