Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Database Design Help Needed

    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.
    Click image for larger version. 

Name:	table.png 
Views:	54 
Size:	45.3 KB 
ID:	9156
    Thanks and best regards,

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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),...
    , ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    2

    Database Design Help Needed

    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,

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    elaboration: there is one row for every cell, except the diagonal

    try it:
    Code:
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    1
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •