Skilirium

Design the physical data model

Activity 6 of 8 in Data modelling · Occupation: Data modeller

The outcome: designs a physical data model for the target platform without introducing anomalies.

Builds on: Derive the logical data model.

Which AI model is best at design the physical data model?

Best result for design the physical data model: gemini-4-argon (100% test score).

ModelProviderTierTest scoreKnobits mastered (expected)Cost per test run
gemini-4-argonGoogleFlagship100% 98%$0.062
gemini-3.1-pro-previewGoogleFlagship81% 84%$0.20
gpt-6-astraOpenAIFlagship91% 97%$0.45
claude-fable-5-1AnthropicFlagship98% 98%$0.85
gemini-3.8-flashGoogleMid tier86% 92%$0.045
gpt-6.1-solOpenAIMid tier86% 92%$0.077
claude-sonnet-5-5AnthropicMid tier94% 94%$0.23
gemini-3.5-flash-liteGoogleLow cost60% 68%$0.008
gpt-6-lunaOpenAILow cost76% 90%$0.005
claude-haiku-5-5AnthropicLow cost78% 92%$0.018

Test score: mean rubric score on the test tasks for this activity. Knobits mastered: expected share of the activity's knobits the model masters. Cost: mean API cost of one test run at list prices. Method and caveats.

The 40 knobits of this activity

13 that AI does well, 4 it does partly, and 0 where people still lead.

IDKnobitTypeAI exposure (models mastering)
K-601Recognises an unmanaged duplicate columnSkillAI does this well 10/10
K-602Recognises logical entities merged into one tableSkillNot tested yet
K-603Recognises a stored derived or summary valueSkillAI does this well 10/10
K-604Recognises a lossy or unconstrained vertical splitSkillNot tested yet
K-605Recognises an entity split into clone tables by valueSkillNot tested yet
K-606Recognises rolled-up subtypes losing conditional constraintsSkillAI does this partly 4/10
K-607Recognises rolled-down subtypes losing supertype constraintsSkillNot tested yet
K-608Recognises a hierarchy encoded redundantlySkillNot tested yet
K-609Recognises content held outside the database by referenceSkillNot tested yet
K-610Recognises a surrogate key without natural-key uniquenessSkillAI does this partly 6/10
K-611Recognises a surrogate value with meaning, reused or renumberedSkillNot tested yet
K-612Recognises an undeclared foreign keySkillNot tested yet
K-613Recognises a referential action destroying what should surviveSkillNot tested yet
K-616Recognises a changeable value list fixed in the DDLSkillNot tested yet
K-617Recognises a physical domain differing from the logical domainSkillAI does this partly 4/10
K-618Recognises binary floating point used for an exact quantitySkillAI does this well 10/10
K-620Recognises a business fact held in physical row orderSkillNot tested yet
K-621Recognises an index set not derived from access pathsSkillNot tested yet
K-622Recognises an overwrite where history is requiredSkillNot tested yet
K-623Recognises inconsistent validity periods in a versioned tableSkillAI does this well 9/10
K-624Recognises a reference to a versioned table by business key aloneSkillAI does this well 8/10
K-625Recognises a soft delete the constraints do not understandSkillNot tested yet
K-626Recognises a fact table of mixed grainSkillAI does this well 7/10
K-627Recognises a measure without stated additivitySkillNot tested yet
K-628Recognises a nullable foreign key on a reporting join pathSkillNot tested yet
K-629Recognises a sentinel value standing in for nullSkillAI does this well 10/10
K-630Recognises a null with a business meaningSkillNot tested yet
K-651Explains controlled denormalisationKnowledgeAI does this well 10/10
K-652Explains the implementation options for subtypesKnowledgeAI does this partly 4/10
K-653Explains referential integrity and referential actionsKnowledgeNot tested yet
K-654Explains exact and approximate numeric typesKnowledgeAI does this well 10/10
K-655Explains null semantics and three-valued logicKnowledgeAI does this well 10/10
K-656Explains value sets held as reference dataKnowledgeNot tested yet
K-657Explains index design from access pathsKnowledgeNot tested yet
K-658Explains history strategies and validity-period conventionsKnowledgeAI does this well 9/10
K-659Explains grain and additivity in dimensional modelsKnowledgeAI does this well 7/10
K-660Explains vertical and horizontal partitioning of tablesKnowledgeNot tested yet
K-661Explains consistency of content stored by referenceKnowledgeNot tested yet
K-662Explains the absence of row order in relational tablesKnowledgeNot tested yet
K-641Routes a defect to the deliverable responsible for itSkillAI does this well 7/10