Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>Architecture</h2> <p>Yes, actually. You could implement this using a unified architecture where every object has a unique entry with a unique ID in an objects table. It would also be extensible so you can add additional object types into the relation later by adding a single new table for each object type.</p> <p>Any other architecture other than what I'm about to describe would require exponential growth in the number of tables to add additional relations to all existing object types.</p> <p><strong>Table 1</strong>: <strong>"ObjectTypes"</strong>. This table would store a list of the types of objects you have. It would have three records with names "Page", "Link", and "Area".</p> <ul> <li><strong>ID</strong> int (identity)</li> <li><strong>Name</strong> nvarchar(50)</li> </ul> <p><strong>Table 2</strong>: <strong>"Objects"</strong>. This table would store object instances with unique 64-bit IDs that identify each object's type and give the object a unique human-readable name or description. The database engine handles the uniqueness for you, simply by setting the ID field as an identity column. This is ideal, because it makes it easy to implement any kind of system that would require unique object IDs, such as a permissions system for controlling access to particular pages, links, areas, etc.</p> <ul> <li><strong>ID</strong> bigint identity</li> <li><strong>Type</strong> int (foreign key to ObjectTypes.ID table)</li> <li><strong>Name</strong> nvarchar(200)</li> </ul> <p><strong>Table 3</strong>: <strong>"ObjectRelationships"</strong>. This table represents relationships between any two objects of any type. If it was a strict hierarchy, where an object would have only one parent at a time, then you could actually make it a temporal hierarchy by adding a time stamp field and creating a view that selects the most recent entry for each object. In your case, since you have objects that can belong to multiple other objects at the same time, a strict temporal hierarchy is not necessary; but it's also flexible enough that you could store, for some objects, a single relation per object, and still have an intact temporal hierarchy for a subset of object types. For bi-directional relationships, two records would be required where the ObjectID and RelatedObjectID are swapped. Note that other architectures would also require two records, just in two separate tables. Alternatively, you could simply treat a single entry as a bi-directional relationship, but it would affect how you write and interpret queries.</p> <ul> <li><strong>ObjectID</strong> bigint (foreign key to Objects.ID); also would recommend a clustered index on this field</li> <li><strong>RelatedObjectID</strong> bigint (foreign key to Objects.ID); also would recommend treating this is a directional relationship as ParentObjectID or ContainerObjectID, and you can still add reverse relations for bi-directional relationships</li> <li><strong>[Timestamp]</strong> datetime (SQL Server datetime2(7))</li> </ul> <p><strong>Tables 4 through 2^32</strong>: <strong>"Pages"</strong>, <strong>"Links"</strong>, <strong>"Areas"</strong>, etc., etc., etc. These three or more tables each store information unique to a specific object type. The wonderful thing about this architecture, is that you need add only a single table for each new object type (along with a new record in the ObjectTypes table), because there is always a single table that stores your object relationships.</p> <h2>Querying the Data</h2> <p>If you want to find all links in a page, you could query for direct relationships between pages and links like so:</p> <pre><code>select L.* from Links L inner join ObjectRelationships R on R.ObjectID = L.ID where R.RelatedObjectID = @pageID; </code></pre> <p>or you could find all links in a page indirectly related through areas like so:</p> <pre><code>--//view LinkRelationships = select * from Links L inner join ObjectRelationships R on R.ObjectID = L.ID --//view AreaRelationships = select * from Areas A inner join ObjectRelationships R on R.ObjectID = A.ID select L.* from LinkRelationships L inner join AreaRelationships A on L.RelatedObjectID = A.ObjectID inner join Pages P on P.ID = A.RelatedObjectID where P.ID = @pageID; </code></pre> <p>Assuming you store bi-directional relationships (requires two records per relationship, swapping the ObjectID and RelateObjectID values), you could find all pages that contain a particular link. As I mentioned, other architectures would also require two records, just in two separate tables.</p> <pre><code>select P.* from Pages P inner join ObjectRelationships R on R.RelatedObjectID = P.ID where R.ObjectID = @linkID; </code></pre> <p>This architecture is very flexible and very efficient in terms of storage space (great for avoiding disk I/O bottlenecks), but it requires you to be comfortable with performing joins and thinking of relationships as different "levels" in an hierarchy as demonstrated by the *Level views in the example query. I actually create these views to simplify queries and make them more understandable. </p>
    singulars
    1. This table or related slice is empty.
    1. POCan code-first auto-create triple primary key relational table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTriynko
    1. USTriynko
    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. COI wasn't familiar with "code-first", until I read this, so I'm including a link in case anyone else is unfamiliar with it: http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx
      singulars
      1. PO
      1. USTriynko
    2. COIt's indeed an interresting but I'm wondering how easy it will be to implement this with code-first. It's a good approach if you plan on having many further customization, but it kinda goes against the EF/Code-first approach.
      singulars
      1. PO
      1. USPierluc SS
    3. CO@Triynko - By doing this, you lose almost all of the benefits from navigational properties, lookups, etc.. It's also questionable whether this violates various forms of normalization, and nullifies the benefits of constraints and foreign keys.
      singulars
      1. PO
      1. USErik Funkenbusch
 

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