| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 08/05/2002 : 08:10:23
|
Maria writes "Hi,
I have a beginner question that I hope you could help me with. I have two tables that includes some statistic variables and the codes for some of them that looks like this:
Table A
VarID
VarName
Table B
VarID
CodeID
CodeText
A third table holds all the answers in codes of the statistic survey. The columns are the different variables.
Table C
ID
Var1
Var2
...
How can I join theese tables so that the codetext are shown instead of the codes for instance in table C ? Or check that the codes for the different variables in table C are valid ? Is there a better way to design theese tables ? Thank you.
/Maria" |
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/05/2002 : 09:21:52
|
Based on your table and column naming, it's very difficult to discern what this data is about... Please post your actual ddl (create table statements), some sample data, and the rowset you are trying to generate. Your question is pretty simple, but without the above information, it is difficult to give a precise answer.
Jay White
{0} |
 |
|
|
MariaM
Starting Member
17 Posts |
Posted - 08/05/2002 : 10:02:49
|
Hi again,
Here are the create statements for the tables
REATE TABLE [Statistics] (
[ID] [int] NOT NULL ,
[1] [tinyint] NULL ,
[2] [int] NULL ,
[3] [tinyint] NULL ,
[4] [varchar] (255) COLLATE Finnish_Swedish_CI_AS NULL ,
[5] [tinyint] NULL ,
[6] [tinyint] NULL ,
[7] [varchar] (255) COLLATE Finnish_Swedish_CI_AS NULL ,
 [tinyint] NULL ,
[9] [tinyint] NULL ,
[10] [int] NULL ,
[Fact] [numeric](12, 8) NULL
) ON [PRIMARY]
GO
CREATE TABLE [Variables] (
[VariableID] [int] NOT NULL ,
[VariableName] [varchar] (50) COLLATE Finnish_Swedish_CI_AS NULL ,
CONSTRAINT [PK_Variables] PRIMARY KEY CLUSTERED
(
[VariableID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Codes] (
[VariableID] [int] NOT NULL ,
[CodeID] [int] NOT NULL ,
[Text] [varbinary] (250) NULL ,
CONSTRAINT [PK_Codes] PRIMARY KEY CLUSTERED
(
[VariableID],
[CodeID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
The columns of the "Statistics" table (1,2,3 ...) are variable ID's. In this table there are coded answers for each variable. What I want to do is to be able to see the code text instead of the code for each answer and check if the codes for the answers are valid. I hope that I have explained so yhat you can understand and maybe help me with this. Thanks !
/Maria
|
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/05/2002 : 10:08:50
|
quote:
... some sample data, and the rowset you are trying to generate....
You're 1/3 of the way there....
Does Codes.CodeID reference Statistics.ID? What does this mean...
quote:
The columns of the "Statistics" table (1,2,3 ...) are variable ID's. In this table there are coded answers for each variable.
I think some sample data and the desired rowset would really clear up your request....
Jay White
{0} |
|
|
|
MariaM
Starting Member
17 Posts |
Posted - 08/06/2002 : 09:49:59
|
Hi again,
Here are samples of the tabes:
STATISTICS
ID 1 2 3 4 5 6 7 8 9 10 Fact
1 1 2 2 1997 3 1 1 5 3 1 0,12544
2 1 2 3 1998 2 2 1 5 3 2 0,25698
3 1 1 3 1997 1 2 1 5 3 3 0,56981
4 1 1 2 2000 1 3 1 5 1 3 0,01258
5 2 1 2 2000 1 1 3 4 1 1 0,25697
VARIABLES
VariableID VariableName
1 Gender
2 Agegroup
3 Country
4 StartYear
5 NoOfEmployees
6 TypeOfCompany
7 NoOfOtherCompanies
8 Motive
9 EarlierOccupation
10 Education
CODES
VariableID CodeID Text
1 1 Female
1 2 Male
2 1 15-25
2 2 26-35
2 3 36-45
2 4 46-
3 1 England
3 2 France
3 3 Germany
5 1 1-1000
5 2 1001-5000
5 3 >5000
6 1 Type 1
6 2 Type 2
6 3 Type3
8 1 Motive 1
8 2 Motive2
8 3 Motive3
8 4 Motive 4
8 5 Motive 5
9 1 Occupation1
9 2 Occupation 2
9 3 Occupation 3
10 1 Compulsary school
10 2 Upper secondary school
10 3 University degree
The Variables table and the Codes table have a relationship (Foreign key VarableID). The statistics table have no relationship with theese table though, and that is what I want to create.
The statistics table have one column for each VariableID (1, 2, 3 etc). In these columns only the codes for the variables are stored. I would like to be able to print the text for the different codes instead in this table:
ID 1 2 3 4 5 6 7 8 9 10 Fact
1 Female 26-35 France 1997 >5000 Type1 1 Motive 5 Occupation 3 Compulsary school 0,12544
I also want to be able to check that the codes in the statistics table against the codes table that they are valid. Is there a way to do this or is it better to organize the data som other way ? I hope you understand what I would like to do. Thanks for your help.
/Maria
|
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 08/06/2002 : 10:31:36
|
okokokok, I think I am beginning to see.
First a comment. Don't use reserved words for object naming. 'Statistics' and 'ID' are both reserved words and thus poor choices for name. Additionally '1' and '2' are bad column names.
I don't understand why you have made the design choices that you have. I don't understand why your column names are aliased to another table?
If I were you, I'd create my [Statistics] table with the columns named as they are intendted....
create table [statistics] (
[id] int not null,
Gender varchar(6) not null constraint fk_statistics_gender references genders(gender),
AgeGroup char(5) not null constraint fk_statistics_AgeGroup references agegroups(agegroup),
.... )
Next, there are two ways to deal with constraining the values to be put into the columns. First, you can create check constraints on the columns...for example, you could set up a check constraint on Gender such that acceptable values are 'Male' or 'Female'. The second way, and the way I would most likely go, is to set up 'look-up' table for each of your 'variables' that hold the allowed values....
create table genders (
Gender varchar(6) not null primary key)
create table agegroups (
agegroup char(5) not null primary key)
...you then create a fk constraint in your [Statistics] table to reference the appropriate look up table.
With this methodoology, analyzing the data is greatly simplified, as if you want to know how many Females, is simple a count(*) in your [Statistics] table with no joins needed.
If this model does not perform well enough, for you what you need, you could create the lookup tables with an IDENTITY surrogate key and then have that 'ID' in your [Statistics] table. This may improve some sorting and grouping operations at the expense of needing to lookup the Natural value when the time comes.
Jay White
{0} |
|
|
| |
Topic |
|
|
|
Design
Forum Locked
