One-To-One Relationships

[Scott Gardner]

(I had to laugh after reading back the title I entered for this post.)

 

On page 104 you wrote, "One-to-one relationships in databases aren’t that common as one-to-one relations can alternatively be combined into a single table."

 

I'm coming (back) to PHP from iOS, and in iOS, specifically in Core Data, it is common to use one-to-one relationships to optimize performance. For example, when storing an image or other blob in the database, storing the blob in a separate table related one-to-one prevents the blobs from being loaded until specifically fetched. In this scenario, it might make sense to store a thumbnail of an image in the main table and the full size image in the separate table.

 

Wouldn't this same optimization technique apply here?

[HartleySan]

Welcome to the forums.

 

I would agree with you to the extend that you seem to be making the assumption that storing images and other blobs directly in the DB is okay in the first place. If you go that route, then you are probably right. But then again, if you do not select the blob columns in your queries, then will it really make a difference whether they're in a separate table or not?! I would think that RDBMSs are optimized to the point that they wouldn't bother scanning columns not referenced in select queries. (This point I'm not sure about though.)

 

On a related note, there was a recent discussion on this forum about whether it's better to place images directly in a DB or not, and the conclusion was that in most cases, it's better to store the images in a separate folder, and then store only a reference to the image in the DB.

I can't find the discussion at the moment, but I do recall that it referenced the following SO discussion:

http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay

[Scott Gardner]

Thanks for your response and the welcome, HartleySan!

 

Storing images/blobs in the database is justifiable in certain use cases, and under certain conditions, many of which are noted on the SO you linked to.

 

One software solution I work with now is facing serious challenges associated with multi-location based usage because they architecturally decided to store all images and files in an encrypted disk image.

 

As computers become faster, and db engines are actively developed to take advantage of these advancements, it is conceivable that we will eventually just store everything in a database (or equivalent data persistence layer). And in fact, in Core Data (iOS/OS X ORM), it is encouraged to just construct the model as containing the blob and enable the OS to decide whether to store the file externally or in the db.

 

I believe it's true that excluding a column in a select will mean that data does not get fetched. Although, it's also possible that some db engines or middleware may be doing pre-fetching or other such routines, and of course select *s will just grab everything (take your pick in calling it programmer efficiency or laziness). So for the minimal additional effort, when storing blobs in a db, I would isolate them even when it's a one-to-one relation.

[HartleySan]

 

 

One software solution I work with now is facing serious challenges associated with multi-location based usage because they architecturally decided to store all images and files in an encrypted disk image.

What are some of the challenges you are facing?

 

Yes, obviously as computers become faster, a lot of the things we have to worry about now become non-issues. Also, I'm sure you know your use case better than anyone, but I think without some extensive testing, it's impossible to conclusively say that storing the blob data in a separate table is faster than storing the data in the same table or out of the DB altogether.

I think RDBMSs are incredibly complex pieces of software, and even the designers of such software could not begin to guess what method is faster in many cases without specific testing.

 

Also, I'm not sure I like the iOS/OS X ORM solution of storing your data one way, and letting it decide everything for you, but that sounds like pretty typical Apple. Anyway, it's just my opinion, so I guess it doesn't really matter. At the end of the day, if it works fairly quickly and doesn't cause any problems, then great.