YT YouTube2Text

YouTube2Text → Transcripts → Design Good Schemas - Get a Better Database - Nuri Halperin - NDC Oslo 2023

Design Good Schemas - Get a Better Database - Nuri Halperin - NDC Oslo 2023 — Transcript

by NDC Conferences · 9,083 words · 1,393 segments · language en · Watch on YouTube

Open in transcriber Summarize with AI Download SRT/VTT

Full transcript

0:02uh my name is Nuri I'm going to be
0:04talking about designing a good schema uh
0:08to get better results
0:11um I've been a developer to start with
0:14Delta lot with back end our database or
0:18data structures been doing a lot of data
0:20migrations lately been doing a lot of
0:22c-sharp in my career
0:24also other programming languages python
0:27JavaScript stuff like that
0:30and since I've been around I figured I
0:32put the stock together just to kind of
0:34share my experience about what worked
0:37short term what worked longer term and
0:40this is really about trying to optimize
0:43for longer term so if I break down the
0:46slide into what I'm really looking at
0:50the use case scenario I'm kind of
0:51envisioning here is that as a developer
0:55I want a good schema that I won't regret
0:58later
0:59and we've all been there or maybe
1:01haven't
1:03um that we designed something we thought
1:06it's great we thought it's quick we
1:08thought we fulfilled everything just to
1:10find out that after delivering the first
1:13iteration or the first iteration into
1:15test or living with it for a year or
1:18five uh you discover all of these things
1:21that all of these decisions that seemed
1:23right at the time and turned out not to
1:25be optimal
1:27so breaking it down a little more we
1:30have to ask ourselves well good scheme
1:32assam's color qualifying what is the
1:34word good like what do I mean by that
1:36okay that's a fair question so
1:40the fitness criteria I'm looking for I
1:43need to stay in this song because
1:44otherwise I'm walking off camera okay
1:46it's here so the fitness criteria I'm
1:49looking for are roughly that the schema
1:53really wants to solve a business problem
1:58um
2:00so what would be not a business problem
2:03like making my boss happy would not be a
2:05business problem if it serves a customer
2:07yes it's a big it's a business problem
2:10but if it doesn't it really doesn't
2:11belong in the things I need to solve
2:14so you gotta watch out for requirement
2:16creeps that tell you how to do things
2:18that has nothing to do with the real
2:20business problem you're solving and
2:23within what you're solving for the real
2:25business you want to make sure that it
2:29doesn't uh scope creep into things that
2:33are not in the real world that are
2:35internal technical things that can creep
2:39in and pressure you to make schema
2:41decisions based on that that don't
2:43belong to solving so it's the size of
2:46the actual word that I care about and
2:48the things that serve that fit in the
2:51things that don't maybe a little less so
2:53and the schema should be easy to reason
2:55with right note the future self I need
2:58to read this later I need to communicate
3:01this with a team I need everybody to be
3:03on the same page and understand it I
3:05don't want something so convoluted that
3:08is so difficult to use
3:10one example of that has anybody heard of
3:13eav entity attribute value
3:17type schemas one over there
3:21um
3:21if we have time I can go into this big
3:25anti-pattern for for any kind of medium
3:29to large scale it's a way to represent
3:31things in a very flexible way in
3:33databases schemas that were designed to
3:36be rigid and well defined
3:40the disaster with that is that querying
3:42this thing in any kind of way is is just
3:46kind of horrific and convoluted and very
3:48difficult to reason with
3:51and finally I wanted to be change
3:52tolerant so in the interest of future
3:57self I want to be able to evolve this as
4:01my product evolves as our business case
4:03evolves with a minimal set of impact
4:10and I'm saying model
4:11but we got to talk about this for a
4:13second because that's a design thing
4:16model versus schema so in the title I
4:20put schema and schema is very specific
4:21model the real world is out there it's
4:24well we're in it right we're right here
4:27and this is the domain this is the area
4:30in which we talk about models
4:33so I can model a person I can look at
4:37real world things about a person their
4:40height uh how they're dressed uh their
4:43hair color if they have any uh things
4:46like that
4:47that was a joke sorry okay
4:49thank you
4:51and then in the database world when
4:54we're talking about stuff we're talking
4:55about schema we're talking really about
4:57the structures of how the data is
5:00actually stored and that dictates how it
5:03would be processed and the most
5:06important thing is the design so the
5:09design is moving from the world the real
5:13world the model into the schema
5:18a model represents the
5:21uh real world
5:25did you notice it looks like a smiley I
5:27don't know I kind of liked it uh a model
5:29represents the real world uh so the
5:32model is looking at a person looking at
5:35properties that I can see looking at a I
5:39don't know an oil rig a pipe a whatever
5:43it is we represent a catalog item is a
5:46product right so catalog item belongs
5:49into this idea that we have some kind of
5:52catalog maybe it's a real printable
5:54catalog and there's items that I show in
5:56the brochure and now I show it in the
5:58web but that's representative of the
6:02real world I pick out things that are
6:05observable about the thing I'm trying to
6:08represent so a catalog item has a name
6:13and I give it a name it has some
6:16measurements things like that and it's
6:19the stuff I care about for business
6:21so if I care about its weight then it's
6:25in my model and if I don't care about
6:27its weight it's not in my model as
6:29simple as that
6:31schema on their other hand schema
6:33implements the model so once we came up
6:37with what we care about from the
6:40business perspective from The Real World
6:43then we design we can move into schema
6:46land and schema land is where we get to
6:49speak a different language
6:53because when I say an item has a weight
6:57there is no weight inside computer
7:00language
7:02there's numbers I can pick the right
7:05number for it or the right
7:07representation for whatever I'm trying
7:08to do so I can say oh a weight is just a
7:12decimal representation of the weight in
7:14kilograms or in grams or in pounds or
7:17stone or whatever I want
7:19but there is no weight inside SQL SQL is
7:23its own language
7:24so really we're going to start
7:26translating things that in the real
7:28world were expressed in all of the terms
7:30we know in our own language of what we
7:33speak speaking language and translating
7:36it into schema in some way
7:39and it describes an entity graph
7:42in that when we have things and I have
7:46an item and I have a catalog that's a
7:48graph that's a representation of
7:50relationships between different things a
7:53catalog item to a catalog in invoice of
7:57you know a piece of paper that details
7:59you know the customer and the items they
8:02have in their shopping cart or stuff
8:04like that their prices the totals all of
8:07these kind of things so all of these
8:09things are relationships between things
8:10and
8:12um I just say this is a graph this is a
8:15a representation of nodes with
8:18connections between them
8:21it can describe
8:24constraints on values so that's a domain
8:28of schema so when I say hey I have
8:31countries in the real world that's in my
8:33model
8:34in SQL there is no country's data type
8:38right there's a stringy data type
8:40there's a numeric data type there's all
8:43kinds of things I can say okay well you
8:46know I'll store it in a chart too
8:48because I have ISO two letter codes for
8:51all countries that would be called a
8:53domain so it's a type meaning car Char
8:57whatever you want and it has
9:00restrictions on what values it has it
9:03doesn't have to be restriction it could
9:04be looked as allow us but in any case
9:07I'm describing that when I store this
9:09stuff it better be in that domain
9:12and some databases actually support this
9:16notion and allow you to declare a domain
9:18it's it's a it's a pretty pretty rare to
9:22see that people really use domain more
9:24likely they're using what's called
9:25domain tables which is just creating a
9:28table and saying ah this table is the
9:31universe of all values that could be
9:32ever used for a country
9:35and
9:36your mileage may vary I'm not talking
9:38about specific database implementation
9:41here so
9:43um you know you may find that you want
9:44to use domains you may find that you
9:46want to use the more generic
9:48implementation of domain tables to
9:51restrict which values can go in
9:55and finally the schema layout will
9:59actually impact your performance and
10:01this is where I recommend to not come to
10:03my talk but go and hit the books talk to
10:06your dbas uh go to courses study on your
10:09own read the manual however you find a
10:13way to learn more about the
10:14implementation the engine itself how it
10:17works the better your performance will
10:20be because as it turns out your schema
10:23could be
10:24implemented you can Define tables with
10:28partitions you can define various
10:30indexing strategies clustered indexes
10:33all kinds of things that will
10:35vastly improve your performance or
10:38totally kill it depending on what
10:41choices you make so this is where beyond
10:43the concept of schema and just saying
10:45using generic ddl data definition
10:48language to describe my tables and my
10:52stuff you can go in and use the
10:54optimization that each engine provides
10:57for you in order to create say a
10:59partition table in order to map
11:00partitions to files files groups things
11:03like that those kind of things if you
11:06get deep into it can help you a lot
11:10and those tend to fold in a lot more
11:13knowledge of not just the model but how
11:16your use case is going to lay out on
11:18that meaning how often are you going to
11:20access things at what rate how much data
11:24you expect things like that
11:29and the main thing is I don't want to
11:31regret it right I don't want to make
11:32things that decisions today that I will
11:36regret tomorrow so the question is what
11:38will I regret tomorrow I can tell you a
11:41few things I regretted one is things
11:44were way too complicated too complicated
11:47I I did the eav model once regretted it
11:50wholeheartedly it's a complex way to
11:53represent flexible objects and
11:55relational databases are just not built
11:58for that so every query and everything
12:00we wanted to do it was an uphill battle
12:04downtime when changes were too vast when
12:07I needed to make a change and it
12:10required me to take the database offline
12:12and therefore the applications I talk to
12:13it offline that's very painful
12:16and just lots of work if the changes are
12:20possible but just require a ton of work
12:22that's big investment that's more
12:24planning that's more involvement of more
12:26people that's harder to roll into
12:28production so those are all regrettable
12:31decisions
12:35so in terms of work
12:39I kind of put together this thing and um
12:42it it it's DB engine specific
12:46but I think it's worth staring at for a
12:48little bit
12:50um I tried to say well
12:52how do we break down what is the impact
12:56of change if I want to change something
12:59what does it involve
13:01so in terms of ddl and we're talking
13:04about the data definition language how
13:06my tables are declared what do I declare
13:09I declare that there is a table or there
13:11isn't
13:12right create a table okay that's cheap
13:15just create a new table nobody used it
13:18before what's my dependencies another
13:20just about zero right I can do that all
13:22day
13:24but if I drop a table I gotta ask myself
13:27hey was the stable involved with anybody
13:30else was it part of a graph with other
13:32things in which case you know there may
13:35be a little more impact or high impact
13:37depending on how uh how interconnected
13:40how many foreign Keys it has to other
13:42things
13:44so dropping a table and creating a table
13:47not the same and dropping a table that
13:50has strings and tentacles to other stuff
13:52more expensive
13:54adding a column with a null value
13:57generally considered a zero cost
14:00operation populating it with values can
14:03take a little more
14:05huge huge tables even more but still
14:08it's within the realm of kinda easy
14:09right
14:11but if this was involved with other key
14:16constraints if the column was involved
14:20in some constraints then we have to take
14:23more care it's not a zero time operation
14:27on large tables when you manipulate
14:30columns that participate in foreign key
14:32that's where your complexity suddenly
14:35starts that's where interconnectivity
14:38shows up and then you may have cascading
14:41updates in other places so that costs
14:43more
14:46um talking about dropping column kind of
14:49similar to dropping a table that has
14:51that column because it all has to do
14:53with these interconnected with this
14:55graph of objects that interact with each
14:58other
15:00and finally changing a column type
15:05it kind of depends if your column types
15:08become
15:10um
15:11if if you had a numeric that was too
15:15narrow and you widened it you're really
15:17more accommodating if you're taking a
15:20type and changing it completely it was
15:22string and now it's an INT anybody that
15:24treated it before a string and wanted to
15:26parse it now has to worry about it so
15:30that's a more sweeping change
15:33um and then if you're narrowing
15:35something if you're taking something
15:36that was open and making it more
15:38restrictive
15:39then again you have to worry about
15:42everybody that was using it making sure
15:44their math isn't harmed making sure your
15:47data didn't lose Precision without you
15:50knowing and intending to do that
15:53so it's kind of good I think to map
15:56the impact of the changes I anticipate
15:59or the changes I might experience as a
16:03result of changing constraints to map it
16:06mentally to like what's the cost of this
16:08change
16:09and if you start with that then when
16:12you're designing you say okay I'm doing
16:14this decision
16:15now what if I had to change it what
16:17changes can happen to it and based on
16:20that I can try to project well is it
16:23advisable to do today
16:27So based on what I just said I would
16:29start with
16:30seven ish kind of areas that I marked as
16:35uh errors or things to know or things to
16:38do that might make your life better or
16:40pick data types
16:41opaque data types are data types that
16:44don't represent a single thing or don't
16:48represent anything for the database
16:49itself
16:50a composed string
16:52can be an opaque data type
16:55if I have
16:57full name as a field in my database
17:01or a column in my database I should say
17:03and I always treat it as just full name
17:07my database is just for label printing I
17:10put in the name it's part of the address
17:12is full name it's just a thing that
17:15appears on the line I never care if it's
17:18Nuri Halperin or Bob Smith or Jane J
17:23Smith or whatever it is doesn't matter
17:27but it becomes an opaque data type if in
17:30my business requirements I need to then
17:33go and sort it by last name
17:36because the database has no idea that
17:39within this string
17:40there's some spaces and they mean
17:42something and then that the last part of
17:46it is actually the last name
17:48and sometimes last names are multi-word
17:52last names so actually counting and
17:55going to the last is not going to work
17:56and the database has no idea about it
17:59right the database is just varchar and
18:02varchar right as programmers we think
18:04string but same thing the database even
18:07in string it's opaque because it has
18:09multiple parts
18:11so doing that
18:13messes you up later it's better if you
18:16know that there are constituents to a
18:18thing to break it apart into its
18:21constituents
18:22blob for the same reason a blob is
18:25something that maybe doesn't have
18:27delimiters naturally in it or that they
18:30are not even attainable within the SQL
18:33language a byte string would be
18:38a good example for that where
18:42um you know it's just a sequence of
18:44bytes and sure in programming I know how
18:47to scroll in and use protobufs and hop
18:50into the right byte to extract the field
18:52from it but as far as the database is
18:54concerned it's a blob
18:56image data jpegs inside the database
19:01I I never not regretted putting binary
19:05data in the database like there's an app
19:07for that it's called the file system
19:09it's called buckets
19:12store the pointer
19:13those are things for those are
19:17infrastructure for these things in the
19:19database keep things that are relational
19:21that are property that are processable
19:23processible with logical operators
19:27I can't say image one greater than image
19:30image two makes no sense I can't scroll
19:34in there and even tell you if it's an
19:36exif A J fifth or a gif or a gif can't
19:40say any of that just don't know
19:43and Json and XML suffer some from some
19:46stuff that is very similar except as
19:49humans we can say oh what do you mean I
19:52understand these delimiters
19:54but the database doesn't so don't do
19:57that
19:59incorrect data types you know dates as
20:02strings is the most often seen one in my
20:05experience it's just using the database
20:09uh to store seemingly flexible data type
20:13or just shuttling it from the front end
20:16and having no marshalling so I can show
20:18it as a string again the database
20:21doesn't know how to compare it correctly
20:23at best you will do an ISO
20:26representation that is canonical and you
20:28can do comparisons of greater less than
20:31but then you'll have to break it apart
20:33look at constituent of the time time
20:36zone offset things like that
20:38ugly stuff there is a database data type
20:41for that in the database knows how to do
20:44things like extract the time zone the
20:46minute the second the whatever from
20:48proper date types so use the proper date
20:52time
20:53and finally non-domain values it's not
20:57something you do in your ddl so much but
21:01if in your column you're going to shove
21:03some random value that is not from your
21:05domain
21:06like you have all country codes and then
21:09you represent Country Code Zero for all
21:11of those that you don't know the country
21:13code
21:14that's wrong and putting null is also
21:17not something from the domain
21:19null
21:20is it a type is it a value it's not a
21:23value it's a type right and it doesn't
21:25belong to the country codes there is no
21:27country in the world that is null
21:30right so it's a non-domain value and
21:33non-domain values mean that the database
21:36doesn't know that it doesn't belong
21:38there so all comparisons are going to
21:40run against it as if it's just part of
21:43the domain but in fact it isn't and you
21:46will have to write extra logic to handle
21:48it everywhere
21:49so don't store values that don't belong
21:52to the domain that you intended
21:58how do we fix OPEC data types I created
22:02a I think I talked about
22:05the OPEC data type issues
22:09um
22:12that SQL language is not made to do it
22:14there's limited support and opaque data
22:18types and non-domain values are are hard
22:22to deal with and just inject a lot of
22:25logic and later on you'll have to undo
22:27that or change it or handle it
22:29everywhere it just kind of spiders
22:31throughout your organization so how to
22:33fix it
22:35it's pretty simple it looks cunningly
22:37like
22:38denormalization templates uh except I
22:43don't love the word denormalization
22:46um
22:47you start with a value
22:49you have in your domain something you
22:51need to represent it you stare at it and
22:54you ask yourself is it atomic is it
22:57Atomic yes I can map it directly to a
23:00SQL data type
23:02if it's so that's the top branch on the
23:06train then I've won let's see if pointer
23:09technology works here
23:11so you go in here is it Atomic yes great
23:15good you just encode it as a SQL native
23:19type and you've won you come in and it's
23:22not atomic
23:23and now the question is what's it what
23:26is its non-atomicity
23:28is it because it's actually a set of
23:31properties like you took a dictionary
23:33and tried to shove it into Json
23:37if you did that then this is a key value
23:40really it's this Branch right here
23:44and each key really wants to be a column
23:47the way to represent things in a SQL
23:51database is assigning a column with a
23:53proper data type nanopic
23:56so you take each one of the keys you
23:59turn it into a column name and you stare
24:01at the value
24:02but you're not done because that value
24:05in itself
24:07you have to look at it and ask yourself
24:08again is it Atomic yes okay I've won I'm
24:12done with my column no it's not okay
24:15well ask yourself again is it a
24:18dictionary with keys and values in which
24:20case you want to break it or is it a
24:23sequence of items
24:24maybe you have an array masquerading as
24:27XML or something like that
24:29or maybe you have a train of values
24:32represented by some buffer or something
24:35like that and they are actually an array
24:37or a set of things
24:40so then the answer there is well if it's
24:43a set of things since columns in SQL
24:46better be Atomic that means I need a
24:49different table
24:51so it's going to become a column in
24:54another table with a reference back to
24:57the parent table
25:00so that's my algorithm there
25:07make sense
25:08any questions on that
25:11okay
25:16which brings us to
25:18air 2. I see a lot
25:21multi-entity table
25:22multi-entity table is a generalized
25:25table
25:26it's a table that wants to store kind of
25:28the parent or the base
25:30and also all its children in one row
25:39and the thing about it
25:42is that you can see it when you see rows
25:46which have either pattern or unpatterned
25:49columns that are always null for some
25:52rows and not null for others
25:55and they interleave with rows that are
25:58null for others but not for some
26:02okay it's hard to say it in English
26:04let's look at an example
26:08here we have a table of a catalog item
26:11a catalog item in my catalog has a SKU
26:14SKU
26:17something a code that represents that in
26:20my company
26:21a name every catalog item it's a shirt
26:23it's pants it's something
26:25and it has numeric size and letter size
26:28and if I looked at values for this table
26:30I probably would see that I have you
26:34know School ABC T-shirt size m
26:39and size M will have numeric size as
26:43sorry letter size populated in numeric
26:46size null
26:48versus I'll have you know pants with
26:52size 32 and
26:55um then I'll have the letter size null
26:57and the name so I can kind of look at it
27:00and see okay well the generalization
27:03here is that I have a table and there's
27:05a generality that all items have a
27:09school and a name
27:12but then I have some properties some
27:15columns that are valid for some stuff
27:17but not other and that's because I have
27:21products that are inherently different
27:23in the real world in the real world I
27:26find a shirt and it has an m on the back
27:28or I have a shirt or a top that has a
27:32size numeric on the back and they're
27:34kind of different they're cut different
27:36they're manufactured different they're
27:38aimed at different people
27:40so they are really different
27:42but I made them live together in one
27:45table
27:50so the way to kind of fix this
27:53is to say well I have a catalog item I
27:56still want to be generic there could be
27:58category catalog items corresponding to
28:00different uh things in the real world
28:03but then I have
28:06my numeric size catalog items and I
28:08shortened the name of the table here but
28:11those are numeric sized things
28:14and they have a numeric size
28:16and then I have a letter size
28:19and that lives in a different table
28:22so I'm not going to have null in the
28:24letter size ever
28:26and catalog item that happened to be
28:28letter sized will have a relationship to
28:31the stable and if I join it it will come
28:33out not null and vice versa and if I
28:37ever wanted a view that joined both of
28:39them or all three tables then I would
28:42have a flat thing but I will have it in
28:44different columns and I will know what
28:46to expect because I did this
28:49right I know very easily that this is
28:53um an item that has a letter size
28:55because it will have a school and a
28:57school for letter size dot school and a
28:59letter as letter size dot letter size
29:02coming back in my joined uh expression
29:05to show me what it is so the database
29:09doesn't have to think hard if it was
29:11more like it was on the left I will need
29:14to expose logic
29:18um everywhere to tell if this row
29:20happens to be
29:22wanting to represent things in one way
29:24or another or I will have to package it
29:26as an OPEC data type that's where people
29:28do type as string and just says oh I'll
29:30just do size and it'll be a VAR binary
29:33and let the application worry about it
29:36well great but then what if you want to
29:39sort Shirts by size
29:42well you know your size 30 40 will sort
29:46correctly Maybe
29:47or maybe it looks graphically and
29:49incorrectly and surely you know
29:53SML XL double XL all of those uh extra
29:57small will sort weirdly
30:00so it's just not good for anyone really
30:03to to do OPEC or to have these nulls
30:05either way kind of messes you up
30:10onwards
30:16um
30:17this one is about abnormal form which
30:22means I failed to uh do my process for
30:27storing things atomically and in their
30:30proper tables
30:34so I have my table enrollment here
30:37and enrollment in my university has
30:41people with a first name and a last name
30:44and I place uh
30:47I have a placeholder for the scores for
30:50the exams uh and uh you know the course
30:55belongs to a certain major no it's not
30:57the course it's the professor and
31:00there's a course itself what the course
31:02code is and a teacher and assistant and
31:04stuff like that so A bunch of things
31:06smell here let's kind of go through them
31:09the first thing is I'm limited to the
31:12number of exams that can occur in a
31:14semester or a quarter or whatever system
31:16right because I chose to Pivot this and
31:20just put the exams right there so where
31:22should it have been
31:27a different table that's right
31:29and then if exam final exam hasn't
31:33occurred yet there will be no row it's
31:35not like I have a placeholder because I
31:37know I have it it just doesn't exist at
31:40this point
31:45um in this I will have nulls often
31:47because I fixed the columns which means
31:51that as time goes by I will start
31:55filling in the exam scores and if
31:57somebody missed an exam it will forever
31:59be no
32:01also some of my courses uh just have a
32:04teacher a professor some of them have a
32:07teaching assistant but that means that
32:10rose that there is no teacher assistant
32:12forever will have null in there so we
32:15talked about that that's silly
32:17we have a fixed faculty assignment which
32:20means for extremely uh large classes I
32:24might not be able to accommodate another
32:25ta and other teacher assistance
32:29um I might be duplicating data when I
32:32see these kind of things there's usually
32:34another table which represents the
32:36teacher elsewhere so instead of using
32:39reference properly to just say well
32:41here's a teacher go join if you need
32:43their name or whatever the primary key
32:45was
32:46um
32:47that that is probably duplicating data
32:50which means at some point There's issues
32:52of maintaining consistency between this
32:55table and the other one
32:58um and you're trying to predict the
33:00future you're trying to predict that
33:02this you know
33:03student will actually complete the
33:05course
33:06you're trying to predict that you will
33:08need to accommodate teachers assistance
33:10you're trying to do all kinds of things
33:11all kinds of assumptions that may not
33:13actually Bear in reality
33:22so the effects of that
33:25you will have sparse columns that's the
33:28ones with nulls that may forever be null
33:31may just populate over time it can
33:35decrease your flexibility in case I
33:37wanted to play with how many Tas and
33:39things happen
33:41it may negate the join engine the engine
33:46join optimizations where
33:51um
33:52I have um the the query optimizers know
33:57how to look at the statistics of indexes
33:59and see which query it should do first
34:02if I joined you know all of my students
34:04into courses it knows which one has more
34:08rows and knows how to do it faster if it
34:11gets right statistics but when you have
34:13null columns it doesn't
34:16it just has to kind of guess as to which
34:19of the values in the index you're going
34:21to hit and it's difficult for it to do
34:24without actually scanning the index
34:27and as a result you will have indexes
34:30that need to contain references to all
34:33of
34:34um the values in the columns so even if
34:38you have nulls some engines will still
34:40keep index keys on it or reference to
34:43rows with the null value and that
34:47increases the overall index size
34:49whereas if it was in a side table and
34:51there's only you know 10 Tas in the
34:53whole school well the index size will be
34:5610 entries
35:00and depending on your engine locks and
35:03things like that will also creep up so
35:06as you update tables that are
35:08multi-entity or that are of abnormal
35:11form
35:12you will find that right lock
35:15contentions at a higher level like at
35:18the table instead of just Roblox will
35:21start creeping up because everything you
35:23write is into the table whereas if you
35:25just wanted to add a teacher's assistant
35:27in a teacher's assistant table you're
35:30locking there so now you made all the
35:33locks kind of
35:34compete in this table space
35:42so that's about it and the queries
35:44complex Point uh is is kind of similar
35:47here and the multi-ended table which is
35:51um
35:52that queries need to bifurcate them I
35:55querying here because I'm trying to hit
35:56the teacher and what they teach or the
35:59student or something and they need to
36:01deal with the null ability of certain
36:03fields and say is this person not doing
36:06this exam or the exam hasn't passed yet
36:09we don't know
36:11the exam date hasn't arrived yet sorry
36:13so logic will creep in and be spread
36:17everywhere
36:20this is my attempt at humor
36:24thank you thank you for your grace on
36:27this
36:28um let's talk keys let's talk references
36:32um so in the world of keys
36:37there's what's called a natural key and
36:40a natural key is something that I can
36:42see in the real world
36:44so a natural key whatever I observe in
36:48the outside world in my model
36:50it exists in the model then it could be
36:52natural if it doesn't exist in a model
36:55it is probably not natural
36:58now natural doesn't mean
37:00that it's always non-assigned so if I
37:04have an agency that issued the iso
37:06two-letter code
37:08effectively it's a natural key of
37:11countries
37:12because it's part of a real business
37:15world it's something that an authority
37:18took over and it might be an authority
37:20of mine right I may be Twitter and I may
37:23be issuing or allowing for user names or
37:26handles or whatever system we have and
37:29that becomes a natural key because I
37:32manage it it's a thing so if it's for if
37:36it's a thing then it still kind of
37:39belongs in the natural key part of
37:42things most importantly it's meaningful
37:45and then it's part of the model
37:48it exists outside of my database
37:52artificial keys
37:54are meaningful
37:56they're known
37:57in the real world but they're kind of
38:00foreign to The Entity itself so
38:03countries in a domain of country in if I
38:06my entity was country that's a natural
38:09key but if it's me and my citizenship
38:12my citizenship is not me
38:15I am a person I'm attached to a
38:18citizenship it's foreign to the me It's
38:21associated it's still it's artificial
38:24but it's still observable in some way
38:26shape or form associated in the real
38:29world
38:30and now we go a little further in the
38:32rabbit hole don't slip and fall like the
38:34guy in the icon we have exposed locators
38:38and exposed locators are things that are
38:40in the database but not in the real
38:43world
38:45so those are primary keys
38:47let's say something that's a row ID an
38:54exposed row ID so like an identity
38:56column uuid that I assigned as a primary
39:01key as something a sequence those are
39:04things that are meaningful only to the
39:06database engine
39:07but I can't go up to a person and say oh
39:11year one two three four yeah yeah
39:14like there's no I like Pat him down
39:16there's no one two three four anywhere
39:18in the pockets there's just doesn't
39:19exist it's just an artifact of the
39:21database
39:23which means a lot of times it's also an
39:25artifact of that database instance like
39:28on that machine it's not everywhere
39:31which means I will have drift in other
39:33environments and things like that if I
39:35try to replicate I might have collisions
39:37if it was generated by two different uh
39:40systems things like that
39:43and those are exposed locators the
39:45exposed part here is that it's visible
39:47meaning I can somehow interact with it
39:50from my program this is a worse class
39:52which is the real surrogate key
39:56and these are ones that are not exposed
39:59so as you can imagine internally
40:01databases have identifiers for the
40:04blocks or for the row or things like
40:07that that are not even exposed to the
40:08programmers
40:09and some systems kind of let you use
40:12them by using special functions saying
40:14hey which version of this row do you
40:18have and it will give you a long ugly
40:21number and says this is you know storage
40:23related
40:24so that's very crusty stuff don't use
40:27that ever for uh primary keys
40:34so using meaningless Keys is the error
40:37here if you're using meaningless Keys
40:40things that don't participate in the
40:42real world
40:43either either being artificial or being
40:46just not part of the real world not
40:48natural in any way shape or form
40:53you are really
40:55um
40:56making everything more complicated
41:00um the number of joins you'll need to do
41:02let's say I have country ID 3 on my UI I
41:05can't say what this country ID is
41:09and Dora Angola I I forget but you know
41:11I I don't know right
41:14if I ever want the country code or if I
41:17ever want its name I would have to join
41:19to another table in order to get it if
41:21you use natural keys
41:23and my natural key say is this iso2
41:26letter code or the name of the country
41:28or ISO 3 then it's always there in my
41:31column already it already answers this
41:34question and if I wanted to filter I
41:36could filter through this yes sir
41:42yeah so there will be an impact in the
41:45impact on the change of the natural key
41:47will be judged by how we look at the
41:51impact of change so would that be a type
41:55change
41:56no it would be a domain change right it
41:59would be a
42:01change in the value Universe of that
42:05domain and in case that it's additive
42:08it's kind of similar to
42:10just adding a column that nobody used
42:13before and in case it's a change because
42:15it's primary key I think the gentleman
42:18is hinting at the fact that primary Keys
42:21need a property of immutability or rare
42:24changiness and that would have cascading
42:27effect which means it's harder yeah but
42:30harder does not mean
42:32don't ever do it harder means what is my
42:35main use case scenario for 99 of the
42:38time right
42:40um yeah so good point and I will judge
42:44it by the effort the pain it will cause
42:46me in the future the thing is
42:50I always have to join always always
42:52always always
42:54when I use a circuit key or exposed
42:58identifier
42:59I always have to do it so it's permeated
43:01through the whole thing
43:04and I said oh well they're using
43:06identity column that's fine right but
43:10everywhere in my joins everywhere join
43:12join join join join join and I'm like oh
43:14that's okay like oh my God there's a new
43:17ISO letter code or a country split
43:19happens
43:20it's like happens once once in five
43:23years
43:25it's not everywhere yeah it affects a
43:27lot of things but
43:28so
43:30where where am I going to have more pain
43:32that's kind of my question
43:35I'll take more questions later let me um
43:37continue on here
43:39um so it increases the number of joint
43:42tables great example by the way
43:44increases the number of indexes because
43:47now because I have joins every foreign
43:50key will need an index on the
43:53referencing table
43:55so it will increase that increase the
43:58number of objects because I will need
44:00that index I will need that extra table
44:03it may be sometimes I can have a domain
44:07represented already there
44:09so what if country code is used for
44:12addresses and used for citizenships
44:16do I need another table to just say oh
44:19my God these are country codes I better
44:22have a table for country codes
44:24or can I use a constraint that says hey
44:28in this country we only allow this array
44:31of items and use a check constraint or
44:33use something else another mechanism to
44:36enforce that domain and not use domain
44:39tables it's also possible
44:46um the clustering indexing efficiency
44:48has to do with
44:51with the exact engine so SQL server has
44:53this thing called the clustered index
44:56other databases don't quite do it this
44:59way so uh your mileage may vary there uh
45:03what would happen is when you're issuing
45:06sequences uh for the primary key when
45:10you're writing each right by definition
45:13is just one number higher which means
45:15you kind of right into the table at the
45:18end of the table and if you're updating
45:20it frequently that creates a hotspot
45:23um
45:24other Technologies might have other
45:26Effects by this so
45:28um it kind of this is a place where you
45:31want to really learn about your specific
45:33database and how these things behave
45:35speaking of which that's the next slide
45:38is sequential primary key auto-generated
45:41keys and sequences are surrogate Keys
45:46sequences are not meaningful in the uh
45:49real world they're just invented things
45:52that I somehow glued to my object and
45:56because of that they suffer all of the
45:58things we already discussed they're hard
46:02to migrate among systems so that
46:06happened to me a lot where on day one
46:08you're in developer land on week two
46:10you're in production yeah we
46:13threw things in production after two
46:15weeks
46:16um
46:17and then you know on year three we want
46:21to simulate all the real problems we
46:23have in production so we asked the ddbas
46:26hey bring us back all the data from
46:28production into uat or some area in
46:31order for us to simulate and run our
46:34software on real data because our
46:36backend system as developers don't have
46:39all this data in some of the conditions
46:41and things we want to check is on a
46:43variety of real world data
46:45but then we kind of have to keep
46:47resetting everything according to
46:50production sanitize things like that
46:52because there's collision with those
46:54keys and when you have more than one
46:57system then these keys that are
47:00meaningless migrate into other systems
47:03and they're not in sync anymore
47:06because this you know person's country
47:08ID says oh you know give me a list of
47:12this country ID and another system and
47:14the other system that country ID has
47:16already changed because it's meaningless
47:18it now collides with a different value a
47:23different row altogether
47:25so it's harder to keep in sync
47:27um hotspot risks we spoke about and the
47:32drift among environments can cause a lot
47:35of
47:37a lot of havoc on its own also
47:40discourages schema migration so that
47:44will come up in an later slide but I'll
47:47talk a little bit about schema
47:48migrations
47:51so sequential primary keys are
47:55essentially exposed locators
47:58most people don't talk about the
48:00difference between exposed locators and
48:02surrogate keys so we'll call it circuit
48:05key to adhere with the world
48:08so what I want from a primary key I want
48:11it to be
48:12familiar I want it to be something that
48:15somebody knowing the domain will say oh
48:17yeah I get this
48:20right an email address a username fine
48:23sometimes a number is okay if that
48:25number means something maybe it's your
48:27ID number national ID something like
48:29that it's okay
48:31you want it to be stable
48:33as a gentleman stated
48:36um if things change what then
48:39Well yeah if things change we'll need to
48:42make changes the question is how rare is
48:45it so when we say it's stable we don't
48:48mean immutable forever we mean mostly
48:51immunable
48:53uh so mostly immunable means that
48:55sometimes it will change on their very
48:58rare condition
48:59and then we'll have to deal with it most
49:02relational databases also a lot of the
49:05nosql databases
49:07require you to keep to require not
49:10require impose that the primary key is
49:13immutable
49:15those that support transactions allow
49:18you to change that value under a cover
49:20of a transaction
49:22which means you basically delete the old
49:24row insert a new row in its place with a
49:27change value under one transaction cover
49:32the operation can be long running if
49:34there's a lot of dependencies or pretty
49:36short but in any event we expect that it
49:40is rare
49:41so meaningful uh rarely changes
49:46and convenient it's simple and compact
49:50so don't ask me about how to represent
49:54people because it's a first name first
49:56name last name middle oh there's a
49:58collision what else can I do can I Pat
49:59people down do I put their DNA sequence
50:01into the ID no that's a tougher one to
50:06answer but typically it comes with
50:08something I'm making a system for the
50:11immigration services okay it has to do
50:13with IDs it has to do with some other
50:16external IDs that I can attach to it and
50:19make it as something right or I make my
50:22own I say hey your email address is you
50:24I don't care what your name is your
50:26email address is you and you are the
50:28recipient of all of those change
50:30passwords so this is you for all
50:33intensive purposes
50:34or a username or something like that and
50:36then we don't want it to be too many
50:38elements we want it to be somewhat
50:40compact so it's convenient we want it
50:43simple
50:43so we don't have to deal with a lot of
50:46logic to have multiple you know 10
50:48column primary Keys kind of gnarly and
50:51it appears again and again and again in
50:53all your queries so we want it as simple
50:55as possible
51:01and now we switch abruptly to not
51:05talking about the database so much but
51:07talking about development and
51:08development practices
51:11and this is where orm comes into play
51:14and
51:15as a developer I love orm
51:19I love the orm idea the first time I saw
51:21it
51:24um then I went to production
51:28so the trouble with orm is that tables
51:31are not classes
51:32tables are the language that proprietary
51:35SQL language that describes the schema
51:37which has to do with the data layout
51:39which the engines are then optimized to
51:42deal with an answer for
51:44but they're not classes
51:47in a class I don't have pointers to
51:49other things like foreign Keys it's just
51:51a class has properties it's all in
51:54memory as some contiguous something
51:57typically
52:02and the database is not the repo pattern
52:05and orms kind of encourage the repo
52:07pattern which is like oh I have an
52:11entity like invoices
52:13okay well that's my class invoice and
52:15the class has an array of invoice items
52:18each one has a school and a quantity
52:21maybe I copy in the title of the name or
52:24something who cares and it also has a
52:26customer
52:27which is a reference to you know
52:29something so what the orm will happily
52:32do is just go and spider all of the
52:34tables because
52:35you know School items items are many for
52:38one invoice I have a table and I have a
52:40relationship and it will Traverse it and
52:42do the whole thing and and it will
52:44produce just gnarly uh queries in order
52:48to do this and then when I say okay on
52:50top of this a repo essentially what I'm
52:52saying is suck up all of this data into
52:55memory and let me in memory iterate it
52:58as if it's some kind of loop well that
53:02will be either chatty like get me the
53:04next one the next one the next one with
53:06all the joins or it will be chunky like
53:08load everything into memory and let me
53:10do it into memory in memory either one
53:13of those is a heavy operation
53:16and the database engine can't really
53:18help you so it just gets gnarly's
53:20queries executes them on your behalf and
53:23tries to deserialize everything and then
53:25the cost of serializing is also present
53:29deserializing from the database
53:31so I don't like the database repo uh
53:35kind of mentality uh database is a repo
53:38mentality that is imposed through using
53:41an orm
53:44you have a diminished benefit from the
53:46orm in my experience in that you start
53:50with you know oh Entity framework oh
53:52forward generated schema or backwards
53:54generator schema my DBA did a good job
53:57let me just suck it in generate classes
53:59either way this is great for week one
54:04hey Nuri did you manage to start the
54:06project oh yeah I have working code
54:08now put it in production and for the
54:11next five years you're gonna fight it
54:13all of those decisions you have a static
54:16class not a static you have a class that
54:18got declared if you want to make any
54:20changes it has cascading impact into the
54:23database
54:24because now you have to forward generate
54:26tables and you have to do migrations
54:28according to how the orm side vice versa
54:32also same thing changes in the database
54:34now you have to change the classes that
54:37are essentially the dto of your database
54:40right they are classes representing the
54:43schema and it's not exactly the class
54:45you wanted it's a class that got derived
54:47because the orm said so
54:50that's not how I like to live my life
54:54you'll have concurrency issues because
54:56the repo model the repo mentality makes
54:59you think of the invoice as a single
55:01thing but then if you want to change the
55:03invoice you're not thinking about just
55:05changing the element
55:07and if you change just a line item and
55:09change the quantity
55:10the object that was in memory that was
55:13the whole invoice and mapped to a class
55:14that you have at your disposal are not
55:17in sync
55:18so you have to do things like
55:19concurrency tracking change tracking
55:22doing all kinds of back and forth to see
55:25if it changed before you write it all
55:28kinds of stuff like that which would not
55:30be necessary if you went directly
55:34and finally
55:37um it's once established not finally I
55:40kind of mentioned it once you establish
55:42the entity to class mapping it's it's
55:45kind of costly to change it and it's
55:47kind of opaque to change it you make
55:49these changes and you know a lot of
55:50things happen in the background and
55:52chasing it everywhere becomes difficult
55:56so Nuri do you really think RMS have no
55:59place well now I'm a consultant of
56:02course it depends
56:06so the things I say maybe orms are
56:08helpful
56:09are in the arena of
56:12connection management
56:14it brings you some benefit that you
56:16don't have to do it on your own hide
56:19some connection pools or contains a
56:21connection pooling and kind of instant
56:24Singleton instance that you can deal
56:26with or multiple injectable items that
56:29map to the same underlying
56:31infrastructure not the lousiest idea
56:33maybe that's a useful thing it does the
56:37codec work of mapping between the
56:40tabular data format that came from The
56:42Wire into SQL types and into c-sharp
56:45types I like that that that's a good
56:47thing right if I could get a bunch of
56:49stuff a bunch of tables and
56:51automatically marry them to my um
56:54marry them to my to my classes I I kind
56:58of like this but I really don't like the
57:01command marshalling the fact that I have
57:03to go through the orm just to call
57:04stored procedures functions things like
57:07that and personally it just makes no
57:10sense to me you could use the driver the
57:12driver actually does it not the orm so
57:15use the driver directly the abstractions
57:18it provides such as repo patterns and
57:21stuff like that
57:22seem nice but
57:24you know after week two I usually just
57:27fight against the orm instead of
57:28enjoying it so that tells me it's not
57:31that useful
57:33and change detection concurrency uh
57:36handling and all of that I just that
57:37that is something that I really uh
57:40resent every time I encounter it's just
57:44not not fun
57:47and then the last one
57:49uh we'll talk about here number seven uh
57:54is ad hoc ddl so the notion of uh going
57:58and just adding a column by hand
58:01uh that you know I've seen dbas do this
58:04I've seen various degrees of of
58:07responsibility around it the problem
58:10with manual changes to ddl or creation
58:13is that it's not very repeatable or not
58:16reliably repeatable you can't automate
58:19this you can't play this again in every
58:22environment and you can't sometimes tell
58:25what happened if you don't have a good
58:27record of who made the action who
58:30changed things how you're going to know
58:33when Trouble Comes how come it was
58:35working yesterday and not working today
58:37like what is the change when did it
58:39happen who did it for what purpose we
58:41don't know any of this and the foregone
58:43conclusion is well you know use
58:46migration script
58:48so you can use uh you know Flyway liquid
58:52base whatever your system supports or
58:54you love using uh if you're not using
58:56any of those please read up and uh look
59:00at it the nice thing about migration
59:02scripts is you store them as files
59:05the Frameworks themselves let you just
59:08play it you can play it on demand many
59:11of them support the notion of rollback
59:13so if you made a mistake you can revert
59:16having it in automation CI CD I'm sure
59:20we've all talked about in the last 10
59:22years so this is what enables you to do
59:25CI CD with databases highly recommended
59:31um
59:31and that's about it
59:34so my advice in summary is all just use
59:38natural keys to the degree possible use
59:41the database how it was intended to be
59:44used which is store Atomic values
59:47normalized to the degree possible
59:49concise data types for what you're
59:51trying to represent and not wishy-washy
59:53things like Json and XML shoved in a
59:56relational database
59:57come to my later if you want to
1:00:00talk about documents my mom will talk
1:00:02later
1:00:03orms not my favorite at all
1:00:07and if you've ever had to fight your orm
1:00:10and go below it and just hand it a
1:00:12statement you know that the orm didn't
1:00:15help you you know it
1:00:16inside right
1:00:19okay
1:00:20so that is it I'll take questions we
1:00:22have a couple minutes or we don't have
1:00:25minutes
1:00:28okay
1:00:30I can't use natural Keys like a
1:00:32meaningful Keys would you recommend
1:00:35using minions so the question is and uh
1:00:40don't feel obligated to sit down it's
1:00:42past time here
1:00:43um
1:00:44in the case of natural if natural keys
1:00:47don't work would I recommend using a
1:00:50non-natural key so synthetic yes
1:00:53uh exposed identifier or surrogate
1:00:56surrogate
1:00:58it's almost impossible to use pure
1:01:00surrogate but
1:01:02um I I would recommend against it in
1:01:06many all cases in other words Invent
1:01:08Yourself if if nobody else if there's no
1:01:11Authority that lets you identify
1:01:13something then you are the authority and
1:01:16if you are the authority maybe own up to
1:01:19it and become the authority on that like
1:01:20that is what a school is Right a stop
1:01:23stock keeping unit no other manufacturer
1:01:25could tell me what the item number is
1:01:28because their item numbers are private
1:01:30to them I'm the big shop that sells from
1:01:33many manufacturers and they could
1:01:35Collide so I assigned it a stock keeping
1:01:38unit which is a number on the Shelf of
1:01:40how I keep it so I became the authority
1:01:42to that otherwise I would have to say
1:01:45galvanized screw you know M3
1:01:49and I would not be able to distinguish
1:01:53it so how do I distinguish disambiguate
1:01:55it with a school that's completely
1:01:57synthetic or with a dash manufacturer
1:01:59number or with a something I become the
1:02:02Authority for that and I store it
1:02:09okay uh thank you so much
1:02:12um
1:02:13enjoy the rest of them

About this transcript

This page contains the full transcript of Design Good Schemas - Get a Better Database - Nuri Halperin - NDC Oslo 2023 by NDC Conferences, generated from the public captions YouTube serves with the video. The transcript has 9,083 words across 1,393 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to translate the transcript into 100+ languages.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, translation to 100+ languages, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.