Ideal sqlite database design

Question

I am relatively new to db design, so any help here would be appreciated. I am working on developing an iPhone app, and keep getting stuck, so I'm thinking my database design is junk. In my app, I have basically three items I need to pull from a db. A book title value, a chapter number value, and a segment content value(comprised of a number of different records) . The app simply displays a number of different short books. Inside each short book, they choose from which chapter they want, and then clicking on the chapter will load a detailview with a batch of related segments creating the readable content. So a chapter has many segments exclusively related to that chapter. A book has many chapters related related to that book, and there are several different books. For my db design, should each book be its own separate table? With columns for book_title, chapter_number, and segment_content? I'm basically just running into issues trying to output my data because it may not be organized all that well. Any thoughts or insights would be appreciated! Thanks

Answer 1

I know you are asking about Database design, but I would really recommend using Core Data for something as small was what you are trying to model. Then you don't have to worry about all the sqllite details you just deal with objects and that is it. The book is a good reference for all things iPhone, there are more Core Data articles at that link.

• Steve: Hey thanks for the insight. I have contemplated the core data route, but my only hangup is about the data source, where does the data come from? Right now, I have web content I put into a spreadsheet, that then went into an sql db that I am reading from my app. How does it work with core data? I understand how the object models work that they are more conveniently accessible, but my only question is about where I am going to get my content from, because there are thousands and thousands of lines :)
• Joshua Nozzi: You face the same conundrum whether you're using Core Data or SQLite. At some point, you still need to import the data.
• Steve: Ok, so a core data using app still retrieves its info from say an sqlite database? Its just a more convenient way to manage the importing?
• user177800: he means you still need to import the data into "something" whether it is a database or an object model you still need to do the same loading either way. you could easily make a webservice call to get the data and create / update the objects, that is what I am doing.

Answer 2

Usually you have to create a table for each entity in your application. Your entities are Book, Chapter, Segment. So you have three tables, where row in Chapter references row in Book and row in Segment references Chapter and may be Book.