Help designing database
Posted 12 August 2003 - 11:41 AM
Can you help me out here .
What would be de best way to do this?
Lets say i have a table questions and a table persons .
i want to have every person answer all the questions with a y or n or 0,1.
can i better make a new table person-answers ?
when i do so how to do i make sure that if i make a new person in table persons that this person no also exist in person-answers and has all his answers are 0 by default.
the same would be if i creat a new question i want it autmaticly created in the person-answer for every person with a default value 0.
what would be the best way to do this.
Thanks in advance,
Posted 12 August 2003 - 02:21 PM
Create table question (questionID, question, etc.)
Create table person (personID etc.)
Create table question_person (questionID, personID, answer, etc.)
2.In the create table statements use DEFAULT value for each data item you want to set a default for - so in this case:
...answer int(1) DEFAULT 0,...
3.I know you say you want to create every question for every person with a default value of \'0\' but that defeats the purpose of creating question_person. If you follow step one a new person will appear in question_person when you code it so that they answer questions - only then will they receive a question_person.questionID and be given a default \'0\' for their answer. It sounds like you want to create a un-normalised table in person that codes for NULL values (or default \'0\') (that\'s bad).
Why do you need to see default \'0\' for each question? Surely its better to leave a table unfilled until someone answers a question?
Posted 12 August 2003 - 02:50 PM
maybe it is a bad example .
it is like this ....
i have a table with rules/checks
with rule_ID Rule_code etc. then a table Profiles (profile_ID,Profile name)
now a rule can be set to 0(default) 1 warning 2error.
now i want to save these settings as a profile
should i add a third table with rulesettings_for_profile
with profile_id form the the table profile_ID+rule_ID as primary key and one more field like value (the 0,1,2)
Posted 12 August 2003 - 03:06 PM
Ah! Yes, you have answered your own question.
I would say it is dependent on the size of your database. If small, you may as well keep the rule profile inside each user(?) to save query time (and since you are coding default values into a table anyway). However, it is good practice to normalise your tables (especially if the DB is larger), therefore the JOIN table is more sensible.
Posted 13 August 2003 - 09:07 AM
ok lets say i choose to normalize my database a bit\' should this be oke ?
CREATE TABLE rules(Rule_id INT NOT NULL AUTO_INCREMENT,Code TEXT NOT NULL,cat TINYTEXT,sub_cat TINYTEXT,description TEXT,PRIMARY KEY (Rule_id),UNIQUE UC_Rule_id (Rule_id));CREATE TABLE Acces(User VARCHAR(8) NOT NULL,Password VARCHAR(5) NOT NULL,PRIMARY KEY (User));CREATE TABLE profile(profile_id INT NOT NULL AUTO_INCREMENT,profile_name TEXT,description TEXT,author VARCHAR(8) NOT NULL,User VARCHAR(8),FOREIGN KEY (User) REFERENCES Acces (User),PRIMARY KEY (profile_id),UNIQUE UC_profile_id (profile_id));CREATE TABLE Profile_settings(setting TINYINT DEFAULT 0,profile_id INT NOT NULL,Rule_id INT,FOREIGN KEY (profile_id) REFERENCES profile (profile_id),FOREIGN KEY (Rule_id) REFERENCES rules (Rule_id));
Posted 13 August 2003 - 12:53 PM
there for i have another question .
i want to get all rules with a certain profile id. but i want them order by rule.cat
so something like:
SELECT rules.rule_id,rules.description,rules.cat,profile_settings.setting FROMrules,profile_settingsWHERE rules.rule.id=profile_settings.rule_id AND profile_settings.profile_id=0
i filled table rules ,profile ,profile_setting, acces.
so rule_id=o exists as wel is profile_id=0 and profile_setttings.setting
What am i doing wrong then.
please advise .
Thanks in Advance
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users