Help designing database

[VLA]

Hello Guy\'s ,

 

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,

 

Danny

[michael yare]

1.You need to link your tables with question_person, yes.

 

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?

[VLA]

First of all thank you for replying

 

 

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)

 

Thanks Danny

[michael yare]

You\'re welcome.

 

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.

[VLA]

Thanks again!

 

 

ok lets say i choose to normalize my database a bit\' should this be oke ?

 

 

SQL structure:

 

 

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));

 

diagram

 

 

 

??

[michael yare]

Looks sweet, but shouldn\'t profileID be a FK in access (since access seems to be the user table)?

 

That way a user has a profile, as opposed to a profile having a user?

[VLA]

mmm wel you would be right if the it was a user profile but in this case profile is a set of rules... and is not related to a user

 

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 FROM

rules,profile_settings

WHERE 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