Hi guys,

I have a database consisting of a number of tables to record student information. This data comes from a form that the student need to fill out.
In it there are entities like 'relationship,internet access,would you like to share your details with others, etc' How do i go about designing a table for these kind of 'random' data. I want the normalization of data rules to apply.

What have you covered so far in class? Have you approached either the professor or a TA about this? Since they know what material has been presented in class and a great deal more about the assignment than what you've posted, they can offer much better help than we can.

-PatP

pat this is not an assignment in class. A real software that has to be developed and I would like some suggestions about design techniques. I have an idea of the design but I want to know if it wont impact performance. I have a student form consisting of Qu like:

- Do you have internet access
- Relationship
- Do you want to pay by cheque, credit card + other options
- etc

So I wanted to create 2 tables as thus:

tblDataTypes

-id
-value


tblRelations

-ID
-parentID
-childID

Examples:

tblDataTypes:
ID Value
1 Relationship
2 Internet Access
3 Payment
4 Married
5 Single
6 Divorced
7 Yes
8 No
9 Cheques
10 Credit Card
etc

tblRelations:
ID parentID childID
1 1 4
2 1 5
3 1 6
4 2 7
5 2 8
6 3 9
7 3 10
etc

I did it this way so that even if more questions are added after, the table does not have to be re-built, instead having the parent child relationship, i can easily extend the form data.
The problem am having is now, if i want to relate these tables to the students, how do i do that. Would i

1) do a table consisting of say 10 columns for 10 different questions with their id's as foreign:
ex:
tblsurvey
ID studentID internetAccessID relationshipID paymentID ...
1 1 4 2 6

The foreign keys above are obtained by quering the 2 tables above and the primary key of tblRelations inserted in tblSurvey

2) each question relates to studentID
ex:
tblSurvey
ID studentID answerID
1 1 4
2 1 2
3 1 6
etc

Option 2) will have many rows and I think that would impact on performance for number of searches that have to be made while option 1) has a problem of expansion. What if the form needs to be updated and more questions added, so option 1) table's design would have to be re-done.

I hope am clearer on this. Any suggestions?

Classic example of an anti-pattern:

Tony Andrews on Oracle and Databases: OTLT and EAV: the two big design mistakes all beginners make

Your data is in no way "random". Model it using the same techniques you apply for any data model.

could please give some more details? What do you mean by 'data is no way random'? Are you referring to the problem I mentioned right? or my overall design in the attachment? if you are referring to the overall design, could you please give a brief example of how you would do it?

What you have done is treat the Questionnaire as the main event (it may be from the application standpoint, but not from the data standpoint). You have not normalised the data, or modelled it formally; if you did, the Entities would be separated properly, and the relations between them would be much easier to resolve.

Second, you have implemented the classic One True Lookup Table (did you read the referenced article ?), which is a common error, produces well known problems, and totally complicates things; at the least it prevents you from using the power of a relational database.

Therefore:
1 Set aside your overall design, and start again (attachment to your design is a hindrance to progress and resolution)
2 Leave the Questionnaire aside for a moment, and focus on identifying the data that you corporation has to deal with, if they are going to process such data.
3 Acknowledge that your ideas of datatypes etc, are classic errors, and if you want to avoid them, set them aside.
4 Focus on the data, independent of Questionnaire and OTLT, which has to be in a database, and normalise it. When you do that, independently, the Entities and Relations will be clear, your original question may not exist; and if it does, it will be easy to resolve.

cheers for your answer Derek.

Yeps i did read that OTLT article. I have wiped out that 'DataTypes' table (no one seems to be pleased with this ) and i've come up with 3 more tables! great! the Questions, Answers and StudentReply tables.

Questions
---------
QID
Question


Answers
--------
AID
QID
Answers

StudentReply
-------------
RID
StudentID
QID
AID

Of course the other entities will be the same as previously normalised:

- Student (First Name, Surname, Title, Nationality, etc)
- Address (address, code)
- Phone (number,code)
- Email (address)
- etc

That sounds like good progress, but I am not sure if that is Normalised or not. These questions are intended to get you thinking, not nec. to answer them specifically in a post.

1 Given:
Student.StudentId is the PK, and StudentReply.StudentId is the FK;
Answer.QuestionId + AnswerId is the PK, and StudentReply.QuestionId + AnswerId is the FK;
We already have a perfectly formed, unique Relational Key StudentReply.StudentId + QuestionId + AnswerId. Disallows duplicates, etc. What is the purpose of StudentReply.RID ?

2.1 The real question (vis-a-vis Normalisation which you seek) is, what is the relation between Student.PK and each answer-to-question ? If 1:1, then each answer-to-question is an attribute (column) in Student.
____EmailPrivate {Y|N}
____OtherDetailPrivate {Y|N}
2.2 What is the relation between Email|Phone|Address.PK and each answer-to-question ? If 1:1, then each answer-to-question is an attribute (column) in Email|Phone|Address.
____Email.IsPrivate {Y|N}
____Phone.IsPrivate {Y|N}
____Address.IsPrivate {Y|N}
(The data is not "random"; either it exists, and you have to manage it, Noramlise it; or it does not exist.)

3 Why is Answer a separate Entity ?
Given Answer.QuestionId and Answer.AnswerId form the PK, what is Answer.Answer ?

4 Is Answer Entity is fixed list of valid answer values, or a list of answer values (eg. email), unique to each Student ? Aren't the answers already recorded (eg. email) elsewhere ?

5 You have removed the DataType table (good). What is the purpose of the Type table ?

thanks for your reply derek.

1. StudentReply.RID is just an auto-increment identity/primary key for that table. I didnt quite get what you meant by the keys though, like these additions. Let me make a small amendment to those tables once more:

QuestionsTable
---------------
QuestionID (PK)
Question

AnswersTable
-------------
AnswerID (PK)
Answer

StudentReply
-------------
StudentReplyID (PK)
StudentID
QuestionID
AnswerID

2. I dont think I want to include these details in the Student table because what if the form gets updated and more Questions come, then the Student table would have to be modified. I prefer linking them up in the StudentReply table.

In the questions table are things like:
a) Do you have internet access?
b) Which method of payment do you prefer?
etc

So these will go in the QuestionsTable of course. Now for the answers, we have a number of Yes/No. So, I will put just 2 records in the Answers table relating to a 'Yes' and 'No' each with their AnswerID. I wont have to repeat over and over. Also, it is not a 1:1 relationship in the sense that for Address you have the form that gives:
- Residential Address
- Postal Address
For Phone:
- Cellular
- Work
- Home
- Fax

So I guess those would be like in 2 tables Address and Phone tables. Everything is linked up in the StudentReply table then just for the survery form questions (Yes/No, etc)

3. Yes Answer is just a valid set of answers
4. Types table will contain things for the Phone entity, we have:
a) cellular
b) work
c) home
d) fax
Address:
a) Residential
b) Postal
Is that okay or just another OTLT?

sorry if am all confused here...

It is just another OTLT. An OTLT in a spreadsheet.

What is the great problem with adding a column to any table ? The existing structure does not change, and the existing code does not break.

You cannot reasonably model the data when you have either spreadsheet mindset (IDs everywhere, but no proper keys, allowing duplicates everywhere) or OTLT mindset (add attributes on the fly). Forget about both of those, and just model the data.

WIth your spreadsheet/surrogate key mindset, you have already accepted redundant (duplicate) columns. In [1], I was pointing out that you have not only duplicates but triplicates. Get rid of StudentReply.StudentReplyId; the PK is (StudentId, QuestionId, AnswerId). Composite keys, are normal in a relational db; they identify inheritance, or migrated values.

It is good that you do not like duplicates in some places. Now remove the duplicate columns as well; pace the attributes in the correct tables; identify how you are going to prevent duplicate rows in the tables (eg. Student (LastName, FirstName, BirthDate) is a Key, an index). When you are finished, then add surrogate keys (redundant columns) where you really need them.

i dont quite agree with you on this duplicating thing because i was reviewing the post between you and mike_bite. I think he is right. Your design can be a pain when upgrading the scheme. And this design i came up with is agreed upon by most of the guys there including one of my friends here who advised me, and is a top consultant. The two tables design is optimum for expansion compared to your given in that post between mike and you. And he gave you the example there that there are no duplicates. But anywayz, I thank you for your reply and concern over the topic. Cheerz.

I have no comment on mike or his posts. I was answering your question, so let us stick to that. It does not matter if you agree or disagree, the technical evidenced fact, in this particular instance, is the surrogate key is a duplicate, redundant. Now if you want to implement that, and go ahead, that is no problem. That does not change the fact of its redundance. You can implement what you "like" because you want to; there is no need to try and deny the technical facts.

And I will not waste time getting into such "discussions".

People believe something, and then they find justification and validation for it. Someone else may believe the exact opposite, and they will find justification and validation for their belief as well. Notice you are getting both here, you are merely choosing the one that justifies your belief.

The scientific method is about proving facts for yourself, not about getting justification and validation from others. Where that is absent, one goes for the self-validation.

You said you have read the referenced article, and you said most people disagreed with your design ... now you are jumping fences because you have found someone who agrees with you. Just because we do not take the time to argue with such people does not mean they are "right", or that their positions have a scientific basis.

If you go ahead with what is famously known as "common mistake that beginners make", in spite of that being pointed out to you, do so with your eyes open. Following the common mistakes of others, just because there are others who justify and defend their common mistakes, will not lead to success. It leads to one more instance of the same well-known common mistake.