petalmind

Ah, okay.

Yeah, I was thinking about the way how to actually build a non-1NF database. So far my best idea is to store sqlite files as blobs in a different database.

You can't enforce FKs if you just split a database into several, due to regulatory, performance/maintenance, organizational or architectural (m*croservices) reasons.

Enforced PKs in modern setting basically only work in a single uhmmm "transaction domain" (in a sense of ACID). You can certainly use two-phase commits or whatever, but that would kill performance so much that nobody (I think) seriously uses that.

Like, I think most modern "NoSQL" solution doesn't have enforced FKs.

Even in a single transaction domain you will get performance improvements if you get rid of enforced FKs. I remember a very old podcast (maybe 2005 or even earlier), a conversation with Werner Vogels where he explained how they were fixing performance issues with Oracle that they were using at that time. For me it was an eye-opener.

The story basically is that when they created an order, they allocated a new ID for an order, then inserted rows in the "order_items" table, and only at the end they inserted a row in "orders" table. All this was NOT in a transaction.

If there was an error during this, they just left the dangling "order_items" rows, and cleaned them up regularly with the script.

Otherwise, Amazon (the web shop) could not handle the load, and it was 20 years ago on a state-of-the-art Oracle relational database(r)(tm) with all Amazon's resources.

This is when I realized that God is dead and everything is allowed.

Thank you, the first three paragraphs with examples are very useful to think about.

Ah, okay, yes, sorry, thank you!

Ahh true, object oriented approach certainly helps making sense, and I assumed it by default. I'll think about that.

I don't understand that comment. What do you mean "let's hope" if I can easily store a text content of configuration or log into a column?

haha, I don't want to express disrespect to The Ancients, but I'm bewildered at the entire 3NF/BCNF thing, couldn't they just patch 3NF already by including the BCNF fix? Why do we need to bother with "3NF-but-not-BCNF" as a separate concept?

> a lot of things published on the relational model just didn’t have formal rigor, but people interpreted it as such.

I wonder if you may have something that substantiates this claim? I agree that relational theory doesn't look very complicated in principle, and it doesn't seem to be very "productive" as a theory. Compared to group theory, for example.

Thank you!

> I'm seen some articles say that atomic in 1NF means you cannot have things that can be decomposed.

Yeah, this seems to be an vague argument, but it's not clear what it means. If I write "WHERE name LIKE 'A%'", does it mean that I "decompose" the string?

I don't see a difference between that and "where the column with array of languages contains string 'fr'".

> What if the languages available do depend on what the lesson is about?

Then it would be a completely different schema that would just be in 3NF.

If you look at Kent's variations you will see that they work hard (graphically) at making sure that you do not assume dependency between language and lesson.

> Once you have fast random access, it's data structures and algorithms: forget the database cruft.

I mean, yeah, but in practice you have to provide ACID or other invariants, you have to provide distributed processing and storage, you have to provide replication etc., etc.

Also, "once you have fast random access" sounds like "once you have gajillion dollars". I too want a pony!

> 4NF was explained confusingly because it's quite complex theory. The fact the author says 'to be best of his understanding' it is 4nf is kind of telling.

> My 'colleagues' (I am external) claim to know all this stuff, but in reality there is no proof they do; I have never seen them do things correctly with respect to dbs or software in general.

Wait I don't get it. Are you saying that "(instructor_id, skill_id); PK (instructor_id, skill_id)" (and the same for languages) is NOT in 4NF; and I am not right? What is in 4NF, then?

> is not obvious at all without specifying requirements _first_. If there is a requirement that an employee skill depends on the language (ie. an employee can type in a particular language only) - the design with a single 3 attributes table is the right one.

In the "Baseline" section I do exactly that: specify requirements first. Nowhere does it say anything about skill depending on the language, so I assumed that it implies that they are independent.

"Suppose that we’re building a system that lets people find sports instructors. Each instructor has a number of skills they can teach: yoga, weightlifting, swimming, etc. Also, instructors can speak one or more languages: English, French, Italian, etc."

Not sure how to improve this wording so that it was more clear that skills and languages are independent.

> Multivalued attributes discussion is missing several important aspects for example referential integrity. It is not at all obvious what should happen when a language is deleted from languages table.

I'm not sure what you mean here because multivalued attributes as defined in 1977 preserve referential integrity. But array-typed values do not, you're right.

This is a very important consideration, thank you! I did not realize that (because I don't really think that classical enforced FKs are that crucial in modern practice.) But from a theory point of view having a list of IDs is not enforced, yes.

> if you don't express the update rules in a database schema, it must be in the application code. Subtle errors are more likely in that case.

This is a common sentiment but I think that you can always demonstrate a use case that is very much practical, not convoluted, and could not be handled by classical features of relational databases, CHECK primarily (without arrays of any kinds).

A couple of examples is outlined here: https://minimalmodeling.substack.com/i/31184249/more-on-stru...

Basically I'm currently pretty much sure that it's impossible to "make invalid states unrepresentable" using a classical relational model.

Also, I think that if your error feels subtle then you should elevate the formal model to make it less subtle. You can have "subtle" errors when the checks are encoded as database constraints too.

This is a very good graphical demonstration (especially the merged cells), thank you. It is also a variation "2b" from Kent's list (just for reference).

I think that the textual demonstration of the same effect could be found in https://www.cargocultcode.com/normalization-is-not-a-process... ("So where did it begin?" section). I rediscovered this blog post after I published the article.

> Because the meaning of "atomic" didn’t change, nor did the meaning of 1NF.

So what is the meaning of "atomic"? Are array-typed values atomic? (I think you can treat it that way, a lot of lists are immutable in different programming languages.)

> But he betrays very little knowledge of the relational model.

This is absolutely possible. Let me ask a couple of questions to borrow from your understanding of relational model.

> Repeating fields, which he proclaims as some kind of innovation, violate 1NF. They’re not atomic.

Sorry, what is "repeating fields"? I don't remember ever using this, how does it look like? Are you talking about array-typed columns? If yes, then why are they not atomic?

Also, I don't think I proclaim absolutely anything is "kind of innovation"? Postgresql supports array-typed columns since 2001, and it was planned in 1986 already as part of other "post-relational" features.

Two schemas "(instructor_id, List<skill_id>); PK(instructor_id)", and "(instructor_id, skill_id); PK(instructor_id, skill_id)" are isomorphic, thus I may be wrong but I don't see any anomalies here.

> Maybe the easiest way to understand it is that the list of languages can’t be used in SQL. If one teacher speaks Italian and French, SQL won’t find WHERE LANGUAGE = ‘FRENCH’.

Ah, so you seem to be talking about array-typed columns. I mean, you can find it easily by using "WHERE languages @> ARRAY['FRENCH']". The difference is mostly syntactical.

If you think that you are not allowed to access single elements of "languages" array because it violates "atomicity" of the value, then we should also say that you're not allowed to use queries like "WHERE name LIKE 'A%'", because they violate the atomicity of a string value. Don't you agree?

> SQL doesn’t recognize repeating fields in a column

Array-typed columns appeared in ANSI SQL:1999, a quarter of century ago. I'm not sure what you're talking about.

> I think the natural 'naive' approach is to have many columns with redundant information inside.

Interesting, thank you. I either don't remember that phase in my own learning, or I was lucky to read about the "correct" approach first. Maybe that's why I was so extra confused about 4NF explanations, haha.

> Things that aren't normalized are "ad hoc," or really just "input."

I agree that every denormalization is "ad hoc". It may not even feel this way because sometimes such ad-hocs become intimately tied into the system performance architecture.

However I'm not sure if I agree with "just input", or maybe that's a different way of saying that: sometimes (often) you denormalize from 3NF to avoid a join. Often you can demonstrate that avoiding join is benefitial, by looking at query plan.

But the problem with teaching is that people become generally more afraid of joins, somehow thinking that every join is killing performance or something. ChatGPT produces a lot of FUD around joins and performance, unfortunately...

Thank you, the second paragraph of your comment is illuminating. There are some more replies in the same vein. I did not understand the mutual independence of "cards", and how you can use a different data layout if you only need to put it on a single card.

> with relational modeling as an academic concept well predating successful implementations of relational databases.

This is also non-obvious, thank you!

Intercom on Sales: https://www.intercom.com/resources/books/b2b-sales

+The Sales Handbook: https://www.intercom.com/resources/books/sales-handbook

> You don't want to store two dates for an event. It's easier to store the start time of the event and a duration for the event.

Frankly speaking you could store all three (begin, end, duration) and just use whatever you need for different purposes. Just introduce a single point of entry/update that keeps alternative representations in sync.

> this should really start with an analysis of the entire problem so as to capture both the static structure of the system (i.e. class model) and its dynamic behavior (i.e. use cases).

How does the deliverable for this sort of analysis look? Do you have a link to some example documents (or maybe chapters in a book or whatever)?

Up to chapter 6 the method is completely database-independent. In chapter 7, a classic relational table approach is explained.

You can choose a different approach if you want to use something like Cassandra, MongoDB or DynamoDB (or some other NoSQL solution). I am not an expert in all three, but I hope some day to write "alternative ending" for this tutorial and show how it can be applied in those environments.

Whatever database you choose, you still must do all the work of logical modeling (first six chapters) for your application. If you don't do that explicitly, you would have just to do that implicitly, intertwined with physical design and thus inherently more confusing.

> This is one of those aspects which is not really touched on in most courses.

Thank you! As I was working on all of this, the more it seemed to me also that what you're saying is true. The goal of this approach is to quickly get a very concrete deliverable that you can confirm with the business people and then proceed with implementing in the specific database, using your favorite table design strategy.

Thank you!

> The term “anchor” feels kind of weird to me

This term was inherited from Anchor Modeling (https://en.wikipedia.org/wiki/Anchor_modeling). "Entity" is pervasive, but I don't want to use this word because it may bring unnecessary baggage and assumptions.

Also, this term is heavily overloaded: basically everything in CS is either an object or an entity, lol.

> The concept of defining the attributes via a question is solid, great way to get clarity quickly.

Thank you, this is an important confirmation for the validity of this approach.

> If we're supposed to go sentinel free does that mean no more numerical types?

sorry what do you mean by "numerical types" here?

You may also be interested in Joumana Medlej's book "Wild Inks & Paints": https://majnouna.gumroad.com/l/wildinks?layout=profile.

"this handbook introduces you to the wonder of transforming natural matter into fully developed art supplies. Learn the basic chemistry behind colours and prepare inks, lakes, paints, pastes, binders, media and drawing tools from plants and materials foraged as the seasons turn, or already present in your kitchen cupboard."

> left outer join

> A join that is guaranteed to keep all rows from the first (left) table. Columns from the right table are filled with actual values if available or with null otherwise.

This wording only works for identity equality join condition. It creates misleading mental model of left joins, and unfortunately is very common.

> add deprecation notices

one thing that I think needs to be more common is that you should not write simply "this method is deprecated".

You must always say "...is deprecated *in favour of* FOO".

It seems that lots of people independently coin this expression. I did it too a couple of years ago. Just checked Google, and there are lots of hits for this.

This is my pet peeve. Top google search results and ChatGPT suggest this "same number of rows" mental model which is incomplete and breaks.

I wrote about this: https://minimalmodeling.substack.com/p/many-explanations-of-...