Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Perhaps the most common way to implement this design is with the "one table per owner type" scheme you mentioned (Tables for Images, "Owner A", "Owner A Images", and repeat for owners B, C, etc). Another common way to implement this is with one "central" table for Images, with the single owner's Id stored within that table. Your criteria are particularly limiting, in that an image may be associated with one and only one owner, but there are multiple types of owner. Implementing such constraints inside the database is tricky, but implementing them outside of the database is much more difficult and problematic for all the usual reasons (application doing the databases work, and what happens when someone modifies the database outside of the dedicated application?)</p> <p>The following is an example of how these structures and constraints might be implemented within the database. It may appear fussy, detailed, and overly-complex, but it will do the job, and once properly implemented you would never have to worry whether or not your data was consistant and valid.</p> <p>First off, all images are stored in the following table. It must be known what "type" of owner an image may be assigned to; set that in ImageType, and (as per the constraints in the later tables) the image can not be assigned to any other kind of owner. Ever. (You could also put a CHECK constraint on ImageType to ensure that only valid image types could be loaded in the table.)</p> <pre><code>CREATE TABLE Image ( ImageId int not null ,ImageType char(1) not null ,constraint PK_Image primary key clustered (ImageId, ImageType) ) </code></pre> <p>Next, build some owner tables. You could have any number of these, I'm just making two for sake of the example.</p> <pre><code>CREATE TABLE A ( AId int not null constraint PK_A primary key clustered ) CREATE TABLE B ( BId int not null constraint PK_B primary key clustered ) </code></pre> <p>Build the association tables, noting the comments next to the constraint definitions. (This is the overly-fussy part...)</p> <pre><code>CREATE TABLE Image_A ( ImageId int not null constraint PK_Image_A primary key clustered -- An image can only be assigned to one owner ,AId int not null ,ImageType char(1) not null constraint DF_Image_A default 'A' constraint CK_Image_A__ImageType check (ImageType in ('A')) -- Always have this set to the type of the owner for this table ,constraint FK_Image_A__A foreign key (AId) references A (AId) -- Owner must exist ,constraint FK_Image_A__Image foreign key (ImageId, ImageType) references Image (ImageId, ImageType) -- Image must exist *for this type of owner* ) -- Same comments for this table CREATE TABLE Image_B ( ImageId int not null constraint PK_Image_B primary key clustered ,BId int not null ,ImageType char(1) not null constraint DF_Image_B default 'B' constraint CK_Image_B__ImageType check (ImageType in ('B')) ,constraint FK_Image_B__B foreign key (BId) references B (BId) ,constraint FK_Image_B__Image foreign key (ImageId, ImageType) references Image (ImageId, ImageType) ) </code></pre> <p>Load some sample data</p> <p>INSERT Image values (1, 'A')</p> <pre><code>INSERT Image values (2, 'A') INSERT Image values (3, 'B') INSERT Image values (4, 'B') INSERT A values (101) INSERT A values (102) INSERT B values (201) INSERT B values (102) </code></pre> <p>View the current contents of the tables:</p> <pre><code>SELECT * from A SELECT * from B SELECT * from Image SELECT * from Image_A SELECT * from Image_B </code></pre> <p>And do some tests:</p> <pre><code>-- Proper fit INSERT Image_A (ImageId, AId) values (1, 101) -- Run it again, can only assign once -- Cannot assign the same image to a second owner of the proper type INSERT Image_A (ImageId, AId) values (1, 102) -- Can't assign image to an invalid owner type INSERT Image_B (ImageId, BId) values (1, 201) -- Owner can be assigned multiple images INSERT Image_A (ImageId, AId) values (2, 101) </code></pre> <p>(This drops the testing tables)</p> <pre><code>drop table Image drop table A drop table B drop table Image_A drop table Image_B </code></pre> <p>(Techincally, this is a good example of a variant on the exclusive type/subtype data modelling "problem".)</p>
    singulars
    1. This table or related slice is empty.
    1. POData Base Design for Images
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USPhilip Kelley
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthis is a seriously long answer... hard to even verify - but looks correct :)
      singulars
      1. PO
      1. USRandy
    2. COI'd heard about this strategy a few months back, and this seemed a good time to try it out. For ease of use you could drop a partitioned view on top of the Image_X tables, but I left that out as it was already messy enough.
      singulars
      1. PO
      1. USPhilip Kelley
    3. COThanks guys for your design, I need try it and I come back to you asap.
      singulars
      1. PO
      1. USGibboK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload