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

Table schemas in relational databases have a huge impact on your future performance and ability to maintain your application. In a world of CI/CD this is amplified since it can hold back your deployments and flexibility.
So how do you design tables that serve your applications well? Is normalization to the Nth degree sufficient? Can't I just throw an index on a table and get good performance? Just develop a POCO/POJO and let your ORM take care of it? (No, no, and no!)
In this session, you'll find out how to step up your schema-design game by adopting a relational mentality. The session will cover aspects of modeling, mapping models to tables, and all the way down to data types and fields.
Check out our new channel:
NDC Clips:
‪@ndcclips‬
Check out more of our featured speakers and talks at
ndcconferences.com/
ndcoslo.com/

[@mw323231A]

My expierence with ORMs has been positive, and when they get in the way I can always use SQL.

[@concretetoy54]

this is the way - maintainer of your code

[@peteruelimaa4973]

Yes, it sounds like he hasn't seen an ORM in a while.

[@TheBayru]

Very enlightening, encapsulates everything I dislike about geo-databases of all sorts (blobs, opaque datatypes, multi-entity-tables, out-of-domain values, ...)

[@InzideEntertainment]

Why stop? I was looking for a complete keyword list.. I generate a report from every tutorial in my discord and screenshot/explain important details for later.. Building the Developers Guild of Canada! Cana-duh...

[@RoamingAdhocrat]

how do you feel about GUID primary keys, except the first one is a generated GUID and the rest are sequential from that

[@f.donnet8165]

masstransit project uses NewId (a compatible Guid type that is sequential and encodes your machine identifier + mac address + process number)... I think it's cool stuff and I will use it. (it respects cluster index too... so)

[@f.donnet8165]

On my side, I think that it's dangerous to use unique key as primary key... this guy thinks otherwise, but I m not convinced.

[@allmhuran]

First let's disambiguate a little - there can be one or more candidate keys. One of those candidate keys is "primary", but this doesn't really mean anything special. For example, in SQL Server if I have a primary key on column A and a unique constraint on column B, then I can create foreign keys from other tables to either column A or column B. OK, so "are GUIDs good candidate keys?". Well, the important point from the talk is that your model essentially MUST have a natural key. As in, there must be something in the model (not just the schema) that allows the unique identification of the relation. A GUID is (almost) never going to be an attribute of any entity in the model. OK, if you have a natural key, is it useful to add a surrogate key of ANY datatype, let alone GUID? Sometimes. In SQL Server there can be a benefit to creating a surrogate key which defines the logical storage order. This is the clustered index. An ever-increasing clustered index is very useful in SQL Server. But what about GUIDs? These are not naturally sortable. They are not created in ever-increasing order. Sql server provides the newsequetialid() function which can be used to generate ever-increasing GUID values for precisely this reason. If you create a surrogate GUID key and define it as the clustering key, and those guids are being generated in random order, either in the database, or by the application, then you will very quickly completely fragment the logical order of data in your database. This is bad. GUIDs are also 16 byte values. That's wide. Ints are only 4 bytes. So, should you *ever* use a GUID? Sure, they still have their use, but the valid use cases are very very rare. So the short answer is - you *probably* shouldn't be using GUID keys, and remember that you must have a natural key. Can a GUID ever be part of the model? Surprisingly, yes. Suppose I am producing invoices. For some reason I decide to use a GUID as the invoice number. This is, amazingly, a "natural" key (although this talk doesn't use "natural key" quite in this way). Why? This is the example where "I became the authority". The invoice number is going to be printed on the invoices that go to the customer. The customer can meaningfully refer to an invoice by its invoice number. This number is a "thing in the world", not merely a "thing in the database". Of course, it would be silly to actually use GUIDs for your invoice numbers. Think about the user experience.

[@oguzhan0Kahyaoglu]

Bu 220v olmasi guzel ama kac saat dayanir acaba kontak kapaliyken?

[@essamal-mansouri2689]

1.5x speed