TheYard's database

Pinch or Ctrl+scroll to zoom; the text is selectable. Source · TheYard

TheYard's database: four tables this application owns, seven ASP.NET Core Identity brings, and the two relationships deliberately left unenforced TheYard's database, as api/TheYard.Database declares it Hand-written DDL is the authority; Entity Framework maps to it and a conformance test fails the build if they disagree (ADR-039, ADR-040). Solid lines are constraints the database enforces. Dashed lines are relationships that exist in the application and deliberately not in the schema. Vehicles the 200-row seed catalogue, read whole in Seq order once at startup Id nvarchar(64) PK Seq int UK, clustered Vin varchar(17) ISO 3779 Year int Make nvarchar(64) Model nvarchar(64) Trim nvarchar(64) BodyStyle nvarchar(32) ExteriorColor nvarchar(32) InteriorColor nvarchar(32) Engine nvarchar(128) Transmission nvarchar(64) Drivetrain nvarchar(16) OdometerKm int FuelType nvarchar(32) ConditionGrade decimal(3,1) exact, not float ConditionReport nvarchar(1024) DamageNotes nvarchar(max) JSON array TitleStatus nvarchar(32) Province nvarchar(64) City nvarchar(64) AuctionStart datetime2(0) local, to the second StartingBid int ReservePrice int null: no reserve BuyNowPrice int null: no buy now Images nvarchar(max) JSON array SellingDealership nvarchar(128) Lot nvarchar(32) CurrentBid int null until bid on BidCount int Photos the vendored stock-photo manifest, read whole in Seq order once File nvarchar(128) PK Seq int UK, clustered Style nvarchar(32) body-style pool Title nvarchar(256) source title Bids the only table that changes after startup UserId nvarchar(128) PK, FK VehicleId nvarchar(64) PK Amount int BidCount int WonBuyNow bit AtMs bigint RowVersion rowversion concurrency token AspNetUsers ASP.NET Core Identity, plus the one column this application adds Id nvarchar(128) PK CreatedAtMs bigint this application's addition UserName nvarchar(256) NormalizedUserName nvarchar(256) UK, filtered Email nvarchar(256) NormalizedEmail nvarchar(256) indexed EmailConfirmed bit PasswordHash nvarchar(max) Identity's, left alone SecurityStamp nvarchar(max) ConcurrencyStamp nvarchar(max) PhoneNumber nvarchar(max) PhoneNumberConfirmed bit TwoFactorEnabled bit LockoutEnd datetimeoffset LockoutEnabled bit AccessFailedCount int Identity's other six Declared because IdentityDbContext expects them; this application writes to none of them. AspNetRoles Id PK nvarchar(128), Name, NormalizedName UK AspNetUserRoles UserId + RoleId PK, both FK, cascade AspNetUserClaims Id PK identity, UserId FK, cascade AspNetRoleClaims Id PK identity, RoleId FK, cascade AspNetUserLogins LoginProvider + ProviderKey PK, UserId FK AspNetUserTokens UserId + LoginProvider + Name PK, UserId FK FK, ON DELETE CASCADE no foreign key: a bid names a synthetic id no join table: the gallery is a hash of the vehicle id Every length was chosen from what the seed dataset actually holds, with headroom, and the reason sits beside the column in the .sql file. Identity's keys are nvarchar(128) and not its default 450, because 450 is 900 bytes and puts every composite key it takes part in over SQL Server's clustered index limit.