vendredi 14 août 2015

how to break many to many relation

out of to the xml file posted below, iam creating 2 tables, called Node and Tag. the Node table :

xml file:

<node id="25454461" lat="49.1628359" lon="8.3868881"/>
<node id="25454468" lat="49.2520203" lon="8.3989846"/>
<node id="25454480" lat="49.1562799" lon="8.393818"/>
<node id="25454484" lat="49.2249985" lon="8.496858"/>
<tag k="railway:maxspeed:diverging" v="200"/>
<tag k="railway:maxspeed:straight" v="280"/>

Node table is structured as follows:

//both (lat,lng) are primary keys
 nodeId     lat           lng
25454461   49.1628359    8.3868881
    ....       .....          .....

Tag table is structured as follows:

  tagTyp                        maxspeed
   railway:maxspeed:diverging     200
railway:maxspeed:straight         280

and the relation betwwe the 2 tables is M:M so I created three tables as follwos

private final String sqlTableNode = "CREATE TABLE "+this.TABLE_NODE+
        " ( "+this.NODE_TABLE_ID_COL+" INTEGER AUTOINCREMENT, " +
        this.NODE_TABLE_NODE_ID_COL+" TEXT NOT NULL, " +
        this.NODE_TABLE_LAT_COL+" TEXT NOT NULL, " +
        this.NODE_TABLE_LNG_COL+" TEXT NOT NULL, " +
        "PRIMARY KEY ("+this.NODE_TABLE_LAT_COL+", "+this.NODE_TABLE_LNG_COL+") );";

private final String TABLE_TAG = SysConsts.TABLE_TAG_NAME;
private final String sqlTableTag = "CREATE TABLE "+this.TABLE_TAG+ //if not exists
        " ( "+this.TAG_TABLE_PK_COL+" INTEGER PRIMARY KEY AUTOINCREMENT, " +
        this.TAG_TABLE_TYPE_COL+" TEXT NOT NULL, " +
        this.TAG_TABLE_MAX_SPEED_COL+" TEXT NOT NULL);";

private final String TABLE_NODETAG = SysConsts.TABLE_TAG_PER_NODE_NAME;
private final String sqlTableNodeTag = "CREATE TABLE "+this.TABLE_NODETAG+ //if not exists
        " ( "+this.NODETAG_TABLE_PK_COL+" INTEGER PRIMARY KEY AUTOINCREMENT, " +
        this.NODETAG_TABL_LAT_COL+" INTEGER NOT NULL, " +
        this.NODETAG_TABLE_LNG_COL+" INTEGER NOT NULL, " +
        this.NODETAG_TABLE_TAG_PK_COL+" INTEGER NOT NULL);";

and what i want to do after the creation of the tables is, to do a query from the "sqlTableNodeTag" to retrieve all columns in the "sqlTableTag" table based on a given (lat,lng). something like,

select * from sqlTableNodeTag where this.NODETAG_TABLE_LAT_COL = latValue And this.NODETAG_TABLE_LNG_COL = lngValue

my question is, the table "sqlTableNodeTag" should contain just the "this.NODETAG_TABLE_TAG_PK_COL" or i have to remove it and add "this.TAG_TABLE_TYPE_COL" and "this.TAG_TABLE_MAX_SPEED_COL"

Aucun commentaire:

Enregistrer un commentaire