search

kevinrae / mosskey

Multiple entry key for North American Mosses
0 stars 0 forks source link

INSERT INTO Map Question #34

Open stephenprae opened 6 years ago

stephenprae commented 6 years ago

Assumption: that INSERT INTO Map (TaxaId, KeyCharacterId) VALUES (20,100); results in inserting flag into that cell for that taxon and character indicating that the character is known to be expressed by that taxon.

kevinrae commented 6 years ago

Ummm. Yes?

stephenprae commented 6 years ago

Just want to be sure as I did not see a value declared in the statement. In every case when using such a statement then a logical ‘true’ is inserted? You know I have a lot to learn and do not want to make and follow through on a flawed assumption. That’s why I ask. Your answers are concise and clear. Thank you.

On Tue, Apr 3, 2018 at 16:50 kevinrae notifications@github.com wrote:

Ummm. Yes?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/kevinrae/mosskey/issues/34#issuecomment-378435105, or mute the thread https://github.com/notifications/unsubscribe-auth/AjUNqrYrmnqHVUELzFBtLghiPObVvu8Iks5tlArQgaJpZM4TF6Dk .

-- Stephen P. Rae, Bryologist - Plant Ecologist Napa Valley California

kevinrae commented 6 years ago

OK I should amend my earlier answer to 'No.'

Much longer answer below...

The 'flag' concept is leftover from the excel / google spreadsheet data model we used earlier. The application doesn't use flags or true/false within the map. Each row in the 'Map' table is equivalent to the 'x' flag you used in the spreadsheet.

But the web app doesn't treat the row as a 'flag' per se.

Instead each row is structured data and we can search and sort on ids for either Taxa or KeyCharacters to find one or more matching rows in the table.

It's a completely different data model. You need more time grokking the schema. :)

The SQL used to search the map for Taxa matching selected KeyCharacters can be found in https://github.com/kevinrae/mosskey-www/blob/master/search_map.php Also... this query is essentially the core of the application logic for the key's most critical function - so understanding what it does - even at a high level - is very useful.

(This is probably the most complex SQL in the app - using subqueries and aggregate functions... Personally I find those concepts hard so it always takes me a while to use these features properly... unfortunately, they were required to get the right data we wanted out of the DB! Anyway, you can see the SQL query doesn't use any BOOLEAN or TRUE/FALSE test to filter results. )

It may help you to compare the contents of the DB tables (i.e. SELECT * FROM table;) with the last iteration of the google sheet. Translating between the data models isn't too hard... but it is a pain... I'm very excited about using the data model in the DB schema exclusively going forward as it frees up a fair bit of mental power for other useful things when I don't have to translate between representations of the data!

stephenprae commented 6 years ago

Changing from yes to no can be brutal! But, response appreciated. Sometimes I think like a dinosaur because my training was before the last ice age. I will stare again at the schema, and spend some time reading your response. Also, going to that URL and think about the content.

BTW, I really appreciate your response. Someday soon I hope to really understand what you have created. Meanwhile I will do my best to work within the design of the schema.

Give me tomorrow to deal with both the response and some minor informational gaps identified by our CPA!

Expect call after dinner.

On Tue, Apr 3, 2018 at 20:16 kevinrae notifications@github.com wrote:

OK I should amend my earlier answer to 'No.'

Much longer answer below...

The 'flag' concept is leftover from the excel / google spreadsheet data model we used earlier. The application doesn't use flags or true/false within the map. Each row in the 'Map' table is equivalent to the 'x' flag you used in the spreadsheet.

But the web app doesn't treat the row as a 'flag' per se.

Instead each row is structured data and we can search and sort on ids for either Taxa or KeyCharacters to find one or more matching rows in the table.

It's a completely different data model. You need more time grokking the schema. :)

The SQL used to search the map for Taxa matching selected KeyCharacters can be found in https://github.com/kevinrae/mosskey-www/blob/master/search_map.php Also... this query is essentially the core of the application logic for the key's most critical function - so understanding what it does - even at a high level - is very useful.

(This is probably the most complex SQL in the app - using subqueries and aggregate functions... Personally I find those concepts hard so it always takes me a while to use these features properly... unfortunately, they were required to get the right data we wanted out of the DB! Anyway, you can see the SQL query doesn't use any BOOLEAN or TRUE/FALSE test to filter results. )

It may help you to compare the contents of the DB tables (i.e. SELECT * FROM table;) with the last iteration of the google sheet. Translating between the data models isn't too hard... but it is a pain... I'm very excited about using the data model in the DB schema exclusively going forward as it frees up a fair bit of mental power for other useful things when I don't have to translate between representations of the data!

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/kevinrae/mosskey/issues/34#issuecomment-378467351, or mute the thread https://github.com/notifications/unsubscribe-auth/AjUNqv5TQ_pmPQ0kxCxWlGuBDq0Ely3Iks5tlDsPgaJpZM4TF6Dk .

-- Stephen P. Rae, Bryologist - Plant Ecologist Napa Valley California