dear Experts,

I have an excel sheet, the screen shot shown in picture attached(table.png) with following example structure

site name|EQX-A|SGP-A|SGP-B|JKT-A|
EQX-A | x | 1 | | 2 |
SGP-A | 1 | x | | |
SGP-B | | | x | |
JKT-A | 2 | | | x |


EQX-A, SGP-A,SGP-B JKT-A are column headers and row headers. Now i want to design an database that can store this information . keeping in mind that sites may increase in future I can not simply design a flat table with this structure.


can anyone help in this regards.

Thanks and best regards,

CREATE TABLE sitexref
( site1 VARCHAR(9) NOT NULL
, site2 VARCHAR(9) NOT NULL
, PRIMARY KEY (site1,site2)
, xref INTEGER
);
INSERT INTO sitexref VALUES
('EQX-A','GSW-A',0),('EQX-A',SGB-P',0),('EQX-A',JKT-B',0),...
,('GSW-A','EQX-A',0),('GSW-A','SGB-P',0),('GSW-A',JKT-B',1),...
, ...

dear r937,

i am using MS SQL Server as my back end and moreover am not an expert this scripting language.

can you please elobrate your design.


thanks and best regards,

elaboration: there is one row for every cell, except the diagonal

try it:

CREATE TABLE sitexref
( site1 VARCHAR(9) NOT NULL
, site2 VARCHAR(9) NOT NULL
, PRIMARY KEY (site1,site2)
, xref INTEGER
);
INSERT INTO sitexref VALUES ('EQX-A','GSW-A',0)
INSERT INTO sitexref VALUES ('EQX-A','SGB-P',0)
INSERT INTO sitexref VALUES ('EQX-A','JKT-B',0)
INSERT INTO sitexref VALUES ('GSW-A','EQX-A',0)
INSERT INTO sitexref VALUES ('GSW-A','SGB-P',0)
INSERT INTO sitexref VALUES ('GSW-A','JKT-B',1)