How to implement a chain of relations?

[trailbarge]

Hi, y'all. I've been bouncing my head off of this one for months as a practical project for teaching myself python & gui development.

I am running WinPython (3.4.3) with PyQt5. Since I am new at this, I should be learning the latest version, right?


Application is an address book. I choose sqlite. Overkill, yes.... but this is training.

TABLE "people"
ID
firstname
lastname

TABLE "contacttype"
ID
type (values will be homephone, mobilephone, email, skype, www, etc.)

TABLE "contactpoints"
ID
code (values will be phone number, email address, etc)

TABLE "contacts" (you can see the many-to-many relationships being built here)
ID
contacttype.ID
contactpoint.ID

TABLE "phonebook" (second generation of many-to-many... this is my problem)
ID
people.ID
contacts.ID

So now I can have each row in "people" stand for a person, who will then have an arbitrary number of phone numbers, email addresses, or whatever.

two tables is easy

def initializeModel(model):
model.setTable('contacts')
model.setEditStrategy(QtSql.QSqlTableModel.OnManua lSubmit)
model.setRelation(1, QtSql.QSqlRelation('contacttype', 'ID', 'type'))
model.setRelation(2, QtSql.QSqlRelation('contactpoints', 'ID', 'code'))

model.setHeaderData(0, QtCore.Qt.Horizontal, "Contact Type")
model.setHeaderData(1, QtCore.Qt.Horizontal, "Contact Point")

Now trying to get that third table "people" to also be represented is something I can not find in any tutorial, doc or video.

my eventual goal is to have a nice window that will allow me or my less-than-database-savvy father to populate the database, then use reportlab or something to build a nice printout.

Any hints? I am specifically flummoxed by building the nested / layered / whatever relation in my relationaltablemodel. All else I mentioned is peripheral.

Thanks for reading!

Perhaps I should also mention that I am also going to have a table of "places" which will be physical addresses, which will be many-to-many relationed with either table "people" or table "phonebook". I am hoping that the minimal example in the original setup above will help me extend the model to include addresses by myself, but if you are aware of any tricks or caveats to a third layer of relations, please let me know. For now... I'm just concentrating on 2-deep relations in my model.

[anda_skoa]

You could for example use a QSqlQueryModel and formulate the SQL query such that it joins data from multiple tables.

Btw, that split between contacttype and contactpoint looks strange, I would have just put that into contact.

Cheers,
_