CODEWITHSUNDEEP
databasedatabase-design
Evik James
Evik James

Reputation: 10483

Should I use "RecordID" as a column name?

I am creating an online application with five sections. Each section will have its own table. Let's call those sections SECTION1, SECTION2, etc. There will be a main table named APPLICATIONS. The first column in that table will be ApplicationID.

There will only be a few thousand records on super fast servers, so I want to focus my attention on table and relationship readability, not on how much processing power I might be able to save if I de-normalize till I am sick.

Here's how I am thinking I should name and structure the tables. Can you confirm this is the most readable method? In the past, I have done this effectively. But, I want to see if there are some easy improvements or ideas to integrate. On a scale of one to ten, how solid is this method of table/column naming?

APPLICATIONS - TABLE  
ApplicationID - pk  

SECTION1 - TABLE  
RecordID- int - pk  
ApplicationID - fk  
Answer1 - text  
Answer2 - text  

SECTION2 - TABLE  
RecordID- int - pk  
ApplicationID - fk  
Answer1 - text  
Answer2 - text

Upvotes: 2

Views: 126

Answers (3)

krtek
krtek

Reputation: 26597

I always name my id columns just id, the table name prefixed is enough to know that id I'm talking about.

In an other topic, your SECTION table seems to have the exact same structure, why not using only one table with a number column or something like this ?

in response to the comments :

SECTION
id - pk
ApplicationID - fk
name

QUESTION
id - pk
text

SECTION_QUESTION
id - pk
SectionID  - fk
QuestionID - fk

ANSWER
id - pk
SectionQuestionID - fk
text

This way you can create your various questions, even share some questions between sections. The SECTION_QUESTION association table map the relation between a question and a section. Then you store the answers into ANSWER which is associated to SECTION_QUESTION instead of QUESTION this way you can know exactly in which section the answer was made.

I hope my proposition is clear.

Upvotes: 2

Oded
Oded

Reputation: 499012

I would go with this structure:

Records - TABLE  
RecordID- int - pk  
SectionID int - fk
ApplicationID - fk  
Answer1 - text  
Answer2 - text  

Sections - TABLE
SectionId int - pk
SectionSequence int

You are duplicating a structure - if this changes (say you need to add a column), you need to apply to change to several tables. DRY applies to databases too.

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

First thing is why do you have section1 and section2 as two different tables? you can merge them into single table with an addition column sectionID and sectionTitle.

Then, your sectionID will be pk in the table and you do not need to use the column RecordID as per your design.

This is as per my understanding from your question, unless you have any specific reason to keep section1 and section2 tables.

EDIT - The answer from @Oded is much better structured.

Upvotes: 0

Related Questions