How do the database tables fit together?

[SandmanNet]

So I'm doing an export of my DVD library and I would rather do it from the sqlite DB than with the xport function from within the application, because then I can automate it better.

But I'm having problems understanding how the tables fit together. What I want is to export all the DVDs and then all the "folders" that I have set up so that I can create a web view of this. This is how I *thought* they fit together, but it isn't working.:

1: select * from ZCOLLECTION

This is what I thought was the categories table. I assumed that the unique id of the category was the field "Z_PK"

2: select * from Z_1ENTRIES

This is what I assumes was the relational table that tells me what DVD is in what folder/category. I assumed that "Z_1COLLECTIONS" was the id of the category "Z_PK" above and that "Z_3ENTRIES" was the id of the DVD.

3: select * from ZENTRY

This gets me all the DVDs, and I assumed that the field "ZUID" was the unique id of the DVD, so matching Z_PK -> Z_1COLLECTIONS <-> Z_3ENTRIES -> ZUID I thought that I would be able to map the DVDs to the different collections. But it didn't work out like that.

So, I seem to have the correct number of items in each collection, but it's the wrong items for some reason. I'm assuming that the Z_1ENTRIES.Z_3ENTRIES -> ZENTRY.ZUID relation is wrong, but changing it to ZENTRY.Z_PK doesn't help either.

I need some help figuring out this relationship Thanks!

[Conor]

You are very close. The ZUID is our internal unique ID that we use inside the program and to identify the cover image (this will be important if you upload the covers folder). Core Data uses the PK as the id for the entries. So your last guess should have been correct.

ZCOLLECTION.Z_PK -> Z_1ENTRIES.Z_1COLLECTIONS
and
Z_1ENTRIES .Z_3ENTRIES -> ZENTRY.Z_PK

ZCOLLECTION: contains all the information for a collection, in this case you would be most interested in the name.

ZENTRY: contains all the information for a movie, the details.

Z_1ENTRIES: contains (Z_1COLLECTIONS, Z_3ENTRIES) that does the mapping of which movie belongs in which collection (a movie can be in more than one collection).

[thajeztah]

Conor, do you have a ER-Diagram of the database? Or would that be confidential information?

[Conor]

It's not confidential information as any good programmer would be able to figure it out from using the sqlite3 ".schema" command or a few lines of Cocoa to print out all the relationships and attributes for a Core Data model. The problem your going to encounter is that Core Data abstracts a lot of the actual SQL table layout. So although the ER-Diagram below is accurate the join tables between a many-to-many relationship are not shown. Also "DVD" is just a subset of "Entry", so all the attributes you will find in "DVD" are part the SQL "zEntry" table. Hope this helps orient yourself with the database layout.

[thajeztah]

as any good programmer would be able to figure it out from using the sqlite3 ".schema" command or a few lines of Cocoa to print out all the relationships and attributes for a Core Data model

True, but I thought it might make things a bit clearer for those without Cocoa/Sqlite experience.
Thanks for the diagram

[SandmanNet]

You are very close. The ZUID is our internal unique ID that we use inside the program and to identify the cover image (this will be important if you upload the covers folder). Core Data uses the PK as the id for the entries. So your last guess should have been correct.

ZCOLLECTION.Z_PK -> Z_1ENTRIES.Z_1COLLECTIONS
and
Z_1ENTRIES .Z_3ENTRIES -> ZENTRY.Z_PK

ZCOLLECTION: contains all the information for a collection, in this case you would be most interested in the name.

ZENTRY: contains all the information for a movie, the details.

Z_1ENTRIES: contains (Z_1COLLECTIONS, Z_3ENTRIES) that does the mapping of which movie belongs in which collection (a movie can be in more than one collection).

Thanks, this sorted it out!