Every table carries the same seven columns

Tables here are not written by hand. sstp_Database_CreateTable creates them, and it stamps the same seven columns onto every one it makes.

PrimKey is a uniqueidentifier marked ROWGUIDCOL, defaulting to NEWSEQUENTIALID() and carrying its own unique index. CreatedBy and UpdatedBy default to SUSER_SNAME(). Authorship is recorded by the database, not by application code that might forget.

The reason this matters is not tidiness. A globally unique key on every row means any row can be referenced from anywhere without knowing which table it came from. That single property is what lets a discussion thread, a task or a photo album attach itself to an animal, an inventory item or a web page through one column — instead of a widening row of nullable foreign keys, one per thing it might point at.

1,109 of the 1,113 user tables carry that key. The four that do not are not application tables. The prefix tells you who owns each one: atbl_ for the 712 application tables, stbl_ for the 397 framework tables that carry the same convention into the code above them. How the procedure layer is organised around the same split →

One tree holds every kind of document

stbl_TeamDoc_Inputs is a single table holding every document in the system. Four columns give it its shape: TeamDocRef says which document a row belongs to, RootRef which top-level branch, SubjectRef which subject, and ParentRef is the self-reference that turns a list into a tree. A Type column says what each row actually is. Fourteen types are in live use.

SortOrder is a float rather than an integer, which is a small decision with a large consequence: a row can be inserted between two siblings by picking a value between theirs, without rewriting the order of everything after it.

The content in that tree is not abstract. Under an Equine Reproduction subject sit branches for reproductive drugs, ultrasound, reproductive physiology, mares and stallions. Elsewhere in the same table, a dated procedure record carries follow-up tasks nested beneath it through ParentRef, each one pointing at a catalogue entry in atbl_Items_Items such as HI: Sucromate (Ovulatory Hormone) Injection or HI: Lutalyse (Prostaglandin) Injection per mL.

One tree means one permission surface, one editor and one search path for all of it. Deletion is soft — Deleted and DeletedBy mark a row rather than removing it — so a thread that ran somewhere unhelpful can be recovered rather than reconstructed. Follow raw data as it becomes knowledge →

Tenancy is a column, not a database

Multi-tenant platforms usually choose between a database per customer, which is easy to isolate and painful to upgrade, and a shared database, which is the reverse. V.E.T.S. shares.

sstp_Database_CreateTable can add a Domain column, defaulting to sfnc_System_GetDomain(SUSER_SNAME()) — a function that returns the CurrentDomain recorded for your login in stbl_System_Users. A row therefore arrives already stamped with the tenant that created it, without the application passing anything. 450 of the 1,113 tables carry that column.

The payoff is that a schema change ships once. There is no migration to run per customer and no drift between one tenant's database and another's, because there is no other database.

This page is a row in that schema

Everything above is easier to trust with an example, and the most convenient one is the page you are currently reading.

It is a row in stbl_TeamDoc_WebPages, holding this markup in a BodyHTML column. That row is joined one-to-one, on PrimKey, to a row in stbl_TeamDoc_Inputs whose Type is WebPage and whose Title is “Technical Architecture”. It sits under a RootRef alongside the other pages in this section, ordered by the same SortOrder float.

It carries Created, CreatedBy, Updated and UpdatedBy exactly as an animal record does, because the same procedure created both tables. The navigation at the foot of this page is generated by reading that tree rather than from a menu someone maintains by hand.

Three ideas, one row: the shared spine, the single tree, and a schema uniform enough that the marketing pages and the clinical records are the same kind of object. Get productive against this schema →