Just can’t wrap my head all the way around it yet. So, I’ve got say author and book tables. So, when adding a book to my database obviously I’m adding a row to the book table. So, say I want to keep this easy for a user and let them add the book’s author on the same data-entry page. I can let them enter the author’s name and last name and create that entry to the author table. so far so good. Now I probably want to loop through the current authors to see if any have the same matching pair of first and last name. not too big of a deal.
BUT once I’ve confirmed the user wants to add a new author or use an existing one, what’s the best practice for making sure the junction table gets updated? Do this separately like "user enters book and author and say some field in the junction table like 'author’s purpose (editor/co-author, etc) and clicks ‘add record’ "
I mean I think I could hack it together, but I’m wondeirng if there are any barebones examples in Xojo anyone knows of?
that is going to depend on how you structure the tables. Im not a DBA but do DBA-ish stuff and I can think of a few ways to handle this based on the scheme. Now if I was a real DBA (and there are several here on the forums) they would come up with other ways in addition.
so the short answer is it depends on your table scheme
[quote]So, say I want to keep this easy for a user and let them add the book’s author on the same data-entry page.
BUT once I’ve confirmed the user wants to add a new author or use an existing one, what’s the best practice for making sure the junction table gets updated?[/quote]
One design pattern goes like this:
You have a ‘create author’ dialog, which can be used at any time.
It creates a record in ‘Authors’, with an ID
On the ‘Create Book’ dialog, you have a search/drop down where the user can select an existing author.
If the author isnt there, they either click to launch a modal copy of the ‘Create Author’ dialog
or
You duplicate that code, and create an Author on the spot.
Now you know the ID (either selected or created)
You save the book, and the book record carries the Author ID
[i]If there is a chance that you want to handle several authors -> one book
and
Author -> several books
Then you may want to consider a link table, which holds
Author ID, Book ID
That means several authors can be linked to any one book, and you dont store the author in the book record.[/i]
Remember that many books have multiple authors. Think of textbooks and other non-fiction type of books.
I’d create the tables similar to: [1] Book [2] BookXAuthor [3] Author
The table [1] Book contains everything about the book including a BookID
The table [2] BookXAuthor contains a uniqueID, BookID, AuthorID, dateCreated, dateUpdated
The table [3] Author contains everything about the author including the AuthorID
With this structure, you can get which authors are related to a book. You can also see what books are related to an author.
[quote=291683:@Kevin Cully]Remember that many books have multiple authors. Think of textbooks and other non-fiction type of books.
I’d create the tables similar to: [1] Book [2] BookXAuthor [3] Author
The table [1] Book contains everything about the book including a BookID
The table [2] BookXAuthor contains a uniqueID, BookID, AuthorID, dateCreated, dateUpdated
The table [3] Author contains everything about the author including the AuthorID
With this structure, you can get which authors are related to a book. You can also see what books are related to an author.
Hope this helps.[/quote]
I would go with something like this myself. It all depends on what your needs are and what you are going to store. I always hated the answer “its depends” but with database schemes/designed that is always the answer.
Thanks everyone! Is there are special (or “best practices”) way to code the foreign keys into these tables in Xojo?
Google fu brings up at lot of examples of junction tables in other languages like this python example:
class ItemDetail(Base):
__tablename__ = 'ItemDetail'
id = Column(Integer, primary_key=True)
itemId = Column(Integer, ForeignKey('Item.id'))
detailId = Column(Integer, ForeignKey('Detail.id'))
endDate = Column(Date)
And while I can understand that, I’m not sure the best way to put that in Xojo. While not really a stumper, it’s kind of a hea-scratching moment for me.
EDIT TO ADD: Does coding the database file by tying the junction table to the other two tables tell the database to throw ny kind of exception like if you update the junction table with a non-existant author ID given the foreign key restrictions (not sure if that is the correct term)?
There is nothing “Xojo specific” about the SQL needed to set up the foreign keys. Just read this: SQLite Foreign Key Support and you should be good to go. Once you set up your tables properly inserting data that violates your foreign key constraints will generate a nice errormessage informing you about the nature of your crime.