1

This is a question about naming conventions in SQL.

How should you name your table and your fields when they are both described by the same word or phrase. Is it ok to have the same name for both of them.

My example:

I am creating a question and each question can have one to many answers so I figured that the best way to do this is to have a structure like this:

CREATE TABLE question
(
    id INT NOT NULL IDENTITY(1,1),
    question VARCHAR(250) NOT NULL
)

CREATE TABLE answer 
(
    id INT NOT NULL IDENTITY(1,1),
    question_id INT NOT NULL,
    answer VARCHAR(50) NOT NULL
)

(The two tables are not that small but I removed some columns for simplicity)

The problem is that I do not think that having a field with the same name as the table does not follow proper naming conventions.

I thought of changing the question and answer column to something like text but unfortunately that is a reserved word and that is against the standard naming conventions, in addition to that I would have to use [] tags when referring to them.

Any recommendations on how to name the columns or the tables?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nick zoum
  • 7,216
  • 7
  • 36
  • 80
  • 1
    "Name" and "Description" tend to be quite popular alternatives. If you are writing in sql server, it is often smart enough to understand keywords as table names without needing square brackets, however the word will still appear highlighted – Ben Sep 25 '16 at 16:33
  • 1
    Perhaps question.content & answer.content ? It's best to avoid field names that would require brackets. (-> no spaces or standard sql keywords) – LukStorms Sep 25 '16 at 17:04

3 Answers3

1

in this scenario i prefer Questions as table name and Question as column name. select question from questions where id=x makes sense. instead of select description from questions or select text from questions or select question from question.

Seethapriyanka Peddada
  • 530
  • 4
  • 7
1

First of all: TEXT is not a reserved SQL keyword. (At least not in the SQL 2003 draft, where I just looked that up).

It is though a data type in SQL Server, and as you are talking of brackets for column names, I guess this is what you are using. (Brackets are also non-standard. Standard SQL uses double quotes for otherwise unusuable column names.) I would not use column names that make the use of brackets or quotes necessary. TEXT shouldn't cause trouble, though, I think. And SQL Server already announces they will remove that type in future versions: https://msdn.microsoft.com/fi-fi/library/ms187993(v=sql.105).aspx

ntext, text, and image data types will be removed in a future version of Microsoft SQL Server.

Anyway, one way to solve the naming redundancy I've often seen is to give tables plural names, so your table answers would have a column answer. An alternative would be answertext or answer_text. However, you can even stick to what you have. It is not bad to have a table answer containing a column answer and it won't cause problems.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
0

have a look into advantureworks Database, for getting the naming convention for key column

You'll get the naming convention for "ID" field is "TableNameID"

follow the next link for more details Database, Table and Column Naming Conventions?

I know this answer should be typed as a comment rather than an answer, but I don't have 50 reputation to comment.

Community
  • 1
  • 1
ahmed abdelqader
  • 3,409
  • 17
  • 36