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
Last activity: Jun 16, 2024, 05:43 PM