Relational Table with self-reference and ambiguous column

Question

I'm writing a relational data model for a system which keeps track of a set of boxes rendered in a browser, each of which contains rendered images.

Currently, there is one table: Box. Each row has x and y coordinate, width and height columns, as well as columns for the type of image content (e.g. mime type) and content location (e.g. a file path or URL).

Some boxes have one layer of nesting - i.e. one box can contain another. In these cases the content type column has the special value 'box' and the location is a self-reference to the Box table.

Some content types (a minority) have extra values associated with the content type, e.g. if the image file is a more complicated multi-page format (e.g. PDF) then I need to store which page will be rendered in the box.

• Can/should I represent this in one table? In this case, extra parameters could be stored (for example) as a query string in the file URL (/path/to/PDF.pdf?page=4).

• How do I handle updating the self-referencing Box rows? i.e. If I delete a box which contains another box, then both rows should be deleted. I can't use a foreign key constraint on the location column, as it can either be a url (for non-nested boxes) or a Box row id (for nested boxes).

Answer 1

I will try to provide some thoughts -

Can/should I represent this in one table? In this case, extra parameters could be stored (for example) as a query string in the file URL (/path/to/PDF.pdf?page=4).

With the above model you have explained, there are possible chances
a. Multiple box with one images (single page format) - with few boxes containing same images.
b. Multiple box with one image (multi page format) - with few boxes containing same/different images of the same PDF.

To make the model more generic, we can assume that each box refers to an image table which can hold anytype of images. This helps to - normalize the table and also may be useful in future expansion (with backward compatibility option).

Assume related to image, you want to added another field (column) - then rather than adding to main table, changing the image table would suffice.

In case of multiple boxes referring to same image, just a reference counter would suffice for the all the set of information to be stored in image table without data duplication.

Ofcourse - additional overhead (I think will be small) would be referring the another table.

How do I handle updating the self-referencing Box rows? i.e. If I delete a box which contains another box, then both rows should be deleted. I can't use a foreign key constraint on the location column, as it can either be a url (for non-nested boxes) or a Box row id (for nested boxes).

With foreign key constraint restriction, one option is in the application logic you need to iterate through the all the boxes till you find the most derived child (box) and delete them first. And then do such things for all the boxes. Idea is similar to deleting a complex tree. But this will increase your application logic which implements the deleting of the box.

HTH!