2

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
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Evik James
  • 10,335
  • 18
  • 71
  • 122
  • Normalizing isn't just about performance, it is about maintainability. This scheme (I can't honestly call it a design) is horrible for maintenance as you will have to add columns every time someone wants to change what is in a seciotn. Learn normalization, then it won't seem to so hard to do. – HLGEM Mar 09 '11 at 22:31
  • I understand normalization. I also understand its advantages and disadvantages. Sure, the advantage is that there's reduced redundancy and more maintainability. But the disadvantage is that it can take much longer to develop. I am the only coder and know the parameters for the project. The questions and responses will not change in the next ten years. Guaranteed. Once it's up and running, the code will be locked down. Also, my strength is more ColdFusion and less SQL. This is a web application with very little data. – Evik James Mar 09 '11 at 23:29
  • @HLGEM, I do completely agree with you that the horrible design is horrible. But, it worked quite effectively on a web site that I built in 2001. That site is still up and running and has stored information accurately and quickly and has generated more than $20 million dollars. At the time I developed it, the CEO assured me that the questions and the responses would never change. He was right. – Evik James Mar 09 '11 at 23:34

3 Answers3

2

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.

krtek
  • 26,334
  • 5
  • 56
  • 84
  • Each section will have up to 50 questions. I don't want to have a table with 250 columns. That is why I am breaking it into logical sections. – Evik James Mar 09 '11 at 16:37
  • You should maybe think of another way to store answers then, if you're planning on adding one column for each question in a given section, this won't be at all extensible and a pain in the ass to use. – krtek Mar 09 '11 at 16:38
  • Krtek, I AM trying to think of another way to store answers. Do you have any ideas on how to do so? – Evik James Mar 09 '11 at 17:47
  • Id is the worst name for an id column, it creates some nasty issues in reporting and itcan create some really bad bugs from people pasting joins in and forgetting to change the alias. It is just a poor idea. – HLGEM Mar 09 '11 at 22:29
  • @HLGEM, I totally agree with your statement. @Krtek, I read your design proposition. It's much closer to what I am looking for. I'll comment above. – Evik James Mar 09 '11 at 23:23
0

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.

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
  • Each section will have up to 50 questions. I don't want to have a table with 250 columns. That is why I am breaking it into logical sections. – Evik James Mar 09 '11 at 16:47
0

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • Oded, thanks for the link to the DRY article. That was a good read. – Evik James Mar 09 '11 at 23:18
  • I left this question open for 3+ months because I wasn't happy with the examples. I've reviewed the questions and answers occosionally. I think you the best solution with the best example code. Thanks for your help!!! – Evik James Aug 01 '11 at 12:28