SQL database - when to use a separate table vs a column of an existing one?

Question

So I am pretty new to SQL and databases in general(only designed a very simple one for a minimal site), and I'm trying to work out the best way to design some models for a heavily DB driven site. So take for example, a user uploaded gallery. I have a gallery table with sensible columns like date uploaded, name, etc., and galleries can belong to one category, of which there will not be that many (at most like 6). Should I have the category be a column of the gallery table? Or have a separate table for categories and have a many to one relationship between the category and gallery tables? I would like to do things in my views like sorting all galleries in a category by date uploaded, is there a performance/convenience difference between these? Having the category be a column of the Gallery table certainly seems easier to deal with than me, but I'm not sure what is the best practice. Thanks.

Answer 1

First of all, you need to understand the conceptual difference.

As a rule of thumb, you are safe to consider the following equivalence:

Table ~~~ Entity

Column ~~~ Attribute

So, when you need to add a new piece of data, in relation to an Entity (an existing Table), the question you can ask yourself is:

Is this piece of data an attribute of the entity?

If the answer is yes, then you need a new column.

For instance, say you have a table describing the Student entity:

Table Student:

[PK] Id
[FK] IdClass
Name
Surname

Say you want to also add the GPA of each student. This is obviously an attribute of the student, so you can add the GPA column in the Student table.

If however you wish to define the Department for each Student, you will see that the department is not an attribute of a Student. The Department is an entity, it exists and has its own attributes outside the scope of the student.

Therefore, the attribute of the student is the affiliation to a certain Department, but not the department itself.

So, you will create a new Department table, and use the Department.Id as a FK in the Students table.

I hope this helps. Cheers.

Answer 2

If you have a one to many relationship between categories and galleries, you want category to be a separate table.

Answer 3

When in doubt use a separate table.

It does not have such a big impact on speed and you will gain more control.