| Author |
Topic |
|
JacobPressures
Posting Yak Master
112 Posts |
 Posted - 2005-08-18 : 12:47:03
|
| Simple question, i just want to get some verification on.I'm recreating my Access DB in SQL Server. But i think i got one of my relationships wrong.You can see the relationship Diagram at www.geocities.com/jacobpressures The relationship i'm concerned about is the one between Diagrams and Questions.The way it is set up currently, one question can have many diagrams. Actually, I need one diagram to refer to several questions so that a diagram can possibly be used more than once.That seems to me that DiagramID should be the foreign key to the Questions table. However, since most questions will not have a diagram, i would have to allow nulls. I don't know if i should do that.I could put in a QuestionDiagrams table allowing 1 diagram for many questions (what i want), and one question with many diagrams (seems like over kill). Since there aren't going to be a ton of diagrams anyway, and a question will probably never have 2 diarams, it seems like overkill.I'm considered keeping the relationship Denormalized (just the way it is on website above). But i risk dublicate filenames. Or NOT deleting all of them out if i decide to get rid of that particular diagram.The most flexible to me seems to go the overkill route (create new table between Questions and Diagrams). The one that is functional (achieves my goals and not more) would work but allow nulls in the Questions.DiagramID column.Any comments? Does everybody vote for overkill?The only real reason i can think of doign the overkill is to be able to show a future employeer a conscientous example of reducing NULLs in the DB.Thanks! |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-18 : 13:28:31
|
| overkill with flexibility up front. it will be more painful down the road to make something flexible from something inflexible. i did'nt look at the diagram but watch that denormalization stuff. incosistent data really gets some people's underwear in a bunch. is the system going to be more DSS or more OLTP?====================================================Regards,Sean RoussyThank you, drive through |
 |
|
|
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2005-08-18 : 14:08:50
|
| Talking to a novice here! What is DSS and OLTP?Thanks! |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-18 : 14:13:16
|
| decision support system (reporting) vs. online transaction processing (data entry).====================================================Regards,Sean RoussyThank you, drive through |
|
|
|
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2005-08-18 : 15:22:32
|
| Its an online exam. |
|
|
|
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2005-08-18 : 17:12:08
|
| Looking at Resource table and Diagrams table.I started wondering if both were needed.The diagrams table was created to present diagrams (or pictures) to users during the examination. For example, a question might ask the user to view the diagram and then answer a question.The resource table was developed for Manuals (PDF), videos (AVI), Audio (MP3) files that could be viewed on the category level before the exam. A person might review the video and then take a test on what he comprehended. He might read an article or manual, and then test on what was read.The Diagram was developed to be view during the test and the Resource was additional info that was to be viewed BEFORE the test began.Typically, a question would not view resource. Only diagrams or pictures were allowed during the exam.Now i've decided to broaden my view of diagram to include the possiblity of video, audio, etc during examination.However, if i keep the tables as is, then some users may add them in both tables destroying the DB integrity.My solution to this problem, i've never done this before, is to destroy the Diagrams table. Keep the 1:M relationship between Resources and Categories and create a relationship between Resources adn Questions. This would allow all media to be stored in one place. I would allow Categories to have many resources and questions to have diagrams.I would then add the associate tables to eliminate the Nulls as Thrasymachus suggested.What do you think? I think it will work, it just looks wierd to me. |
|
|
|
magesh
Starting Member
23 Posts |
Posted - 2005-08-19 : 01:30:12
|
| Hi all,when i looked into the diagram i thinkit will be better to normalize the situation when we have an intermediate relation shiptable between Question table and Resources table( or diagram table)i.e a new (Question_Resources_Rel) table between Question table and Resources table which has 2 main columns such as QID and ResourceIDis this a better way to normalize the situation ??Magesh |
|
|
|
LanMan
Starting Member
1 Post |
Posted - 2005-08-19 : 14:41:09
|
| Hey,I agree on the overkill. Add the intermediate table between Questions and Diagrams to create the so-called many-to-many relationship that is needed here.Lanny SuttonWaiter...Check Please.... |
|
|
|
JacobPressures
Posting Yak Master
112 Posts |
Posted - 2005-08-22 : 14:43:23
|
| Thanks guys! |
|
|
|
|
DB Design Issue