Create multiple sqlite tables, many-to-many design

[Peter Otten]

Chris Angelico wrote:

> On Thu, Aug 15, 2019 at 7:41 PM Gregory Ewing
> <greg.ewing at canterbury.ac.nz> wrote:
>>
>> Chris Angelico wrote:
>> > I prefer to say "Trails" for the table, and "Trail" would then refer
>> > to a single row from that table.
>>
>> That makes sense for a data structure in your program that contains a
>> collection of rows. But I've come to the view that SQL tends to read
>> better if the names of the database tables are singular, because in SQL
>> you're writing assertions about individual rows, not the tables in
>> their entirety.
>>
>> So I would write something like
>>
>>     select T.name, T.id from Trail T
>>
>> but I would assign the resulting list of rows to a variable named
>> "trails" in my program.
>>
> 
> You're selecting from a collection of trails. I don't see a conflict
> here. It's the same as taking a list of values and then filtering it -
> even though assertions are made about individuals, you are filtering
> the entire list:
> 
> early_things = [thing for thing in things if thing.name < 'M']

If list comprehensions were like sql queries then the above would be

early_things = [things.* from things where things.name < "M"]

That doesn't read well as the condition works on one thing at a time.
However, I'm so used to

select things.* from things where things.name < "M"

in the sql context that I still prefer it. Also, in simple queries you can 
omit the table name.