List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:May 6 1999 2:45pm
Subject:Re: MySQL and database design problem
View as plain text  
kalle volkov wrote:
> 
> hi!
> 
> probably not the best list but i'll try ;)
> 
> i have cross-table like:
> ---
>        | first | second | third | fourth
> first  |   5   |    4   |   3   |   2
> second |   1   |    2   |   7   |   2
> third  |   4   |    1   |   3   |   4
> fourth |   5   |    8   |   12  |   10
> ---
> 
> and so on for 200+ rows...
> 
> have to build it on MySQL...
> 
> what is the best solution seeing that there might be 1000+ lines to
> cross :(
> 
> the result should be:
> ---
> SELECT number WHERE first_cross = 'second' AND second_cross = 'fourth';
> ---
> ... which will result the number in the cross of first and second...
> first clause is ALWAYS the upper line and second clause is ALWAYS the
> first column...
> 
> TIA
> 
> regs,
> cal6, in trouble :(

Hi Kalle

I would use a table with three fields:
CREATE TABLE 
	cross 
	(first_cross VARCHAR(30) NOT NULL
	, second_cross VARCHAR(30) NOT NULL
	, value INTEGER
	, PRIMARY KEY (first_cross,second_cross)
	)

Your example data would look like this:
INSERT INTO
	cross
	(first_cross
	, second_cross
	,value
	) VALUES
	('first','first',5)
	,('first','second',1)
	,('first','third',4)
	,('first','fourth',5)
	,('second','first',4)
	,('second','second',2)
	,('second','third',1)
	,('second','fourth',8)
	,('third','first',3)
	,('third','second',7)
	,('third','third',3)
	,('third','fourth',12)
	,('fourth','first',2)
	,('fourth','second',2)
	,('fourth','third',4)
	,('fourth','fourth',10)

This will give you full flexibility on the number of crosspoints currently used.

You select with:
SELECT 
	value 
WHERE 
	first_cross = 'second' 
	AND second_cross = 'fourth'

Tschau
Christian

Thread
MySQL and database design problemkalle volkov6 May
  • Re: MySQL and database design problemChristian Mack6 May
Re: MySQL and database design problemSandrine C.18 May
  • Re: MySQL and database design problemThimble Smith18 May