Sample Header Ad - 728x90

How to reference uploaded blob objects in database tables

0 votes
1 answer
58 views
When there are urls in a database table that represent media files uploaded to my own blob storage: 1) Is it common to link a column relationally to an objects table Ex: - videoHdObjectId: "XXX" - videoSdObjectId: "XXX" 2) Include uploaded urls directly in the table Ex: - videoHdObjectUrl: "https:// ..." - videoSdObjectUrl: "https:// ..." 3) Include both objectIds and objectUrls directly in the table? Ex: - videoHdObjectId: "XXX" - videoSdObjectId: "XXX" - videoHdObjectUrl: "https:// ..." - videoSdObjectUrl: "https:// ..." I started with option 1 where I reference the object table via an id, but in my query to my internal database for facebook ads for example, an ad potentially has a videos table with 5 different references to objects (hdVideo, sdVideo, videoPreviewImage, watermakedHdVideo, watermarkedSdVideo), then images has lots of references to originalSize and resized and watermarked images. If I'm querying for 100 ads, that multiplies out to a lot of subqueries. Is it still appropriate to reference objectIds in this way? ---- Rephrased: I currently have a record of all uploaded videos in a table called objects. In my "ads" table, I need to leftJoin it every time. Is it common practice to link to the id of the object to get the url of the file? I ask only because certain tables have 8-10 references to the objects table due for things like sdVideo, hdVideo, watermarkedVideo, videoPreviewImage etc.
SELECT 
    ads.id, 
    obj1.url AS videoHdObjectUrl, 
    obj2.url AS videoSdObjectUrl 
FROM 
    ads 
LEFT JOIN 
    objects obj1 ON ads.videoHdObjectId = obj1.id 
LEFT JOIN 
    objects obj2 ON ads.videoSdObjectId = obj2.id;
Asked by wongx (109 rep)
Jun 15, 2024, 07:18 PM
Last activity: Jun 16, 2024, 05:43 PM