Death by Database
ovid.github.io
ovid.github.io
What if someone goes to create an address, typos it, matches another customer's address, and then fixes it updating the other customer's address in the process? The other customer might accidentally send their luxury item to a stranger if they aren't paying attention during checkout.
The use case with married couples is compelling, but similar to the previous scenario, what if they got divorced? The one that moves updates their address, which updates their ex's address as well. Same as the other scenario, you might end up with a customer sending their item the wrong place.
These might be edge cases but in general I think it's dangerous to change things like an address without an explicit action from the user.
That said, the story doesn't make any sense to me to begin with (privacy and junk mail concerns notwithstanding):
If an address is to be shared between two people it should probably not include the recipient. But then what mailbox do you address it to if there is more than one person at the address? How do you handle business addresses (where the full shipping label may include a person, an apartment and a company)? If you keep all that info out of the "address", what is even the point of having them as separate entities? If not, how do you avoid accidentally leaking personal information if the user filled out the form incorrectly and you re-match the address to another user? If your answer is "only match if it's an exact match", how do you handle synonymous addresses (e.g. "5th Street", "Fifth Street", "5th St.")? If you treat them as distinct, why bother matching at all?
Or simply: what do you gain from having addresses as separate entities (regardless of whether it's a many-to-one or many-to-many relationship with users)?
If you just want to be able to say "shipping address is identical to invoice address", why not just have one or two addresses per user and never deduplicate? Have the address include the full recipient and stop trying to be clever.
A single person has many addresses. That alone already requires the addresses to be another entity.
I am not sure do I follow correctly but just send marketing to addresses which are linked to actual users. Note that some addresses are orphaned from users but not some other events like orders.
> If an address is to be shared between two people it should probably not include the recipient.
I don't remember details about how recipients were handled. The system was quite simple and just about business addresses. We didn't pretend that address map strictly to single physical place so synonymous addresses were considered distinct. Therefore if a recipient was part of an address that was just another distinction point.
> what do you gain from having addresses as separate entities
> If you just want to be able to say "shipping address is identical to invoice address", why not just have one or two addresses per user and never deduplicate?
In our case customer was always company which may have quite a lot of different addresses for different offices or departments. Addresses also had hight expected reuse and we exposed saved address as a "thing" for a user so that it's easy to browse customers own orders history for different addresses etc.
Uniqueness has been a quite neutral thing. Effectively it just removes case where the same customer has multiple equal addresses... Not much gain nor pain.
Edit: At the end immutable shared entity is not much different from mutable and copied values. That's just one way of doing it and so far it hasn't back fired.
> At the database level, they had a "one-to-many" relationship between customers and addresses.
Which the author goes on to say causes:
> That was their first problem. A customer's partner might come into Bob's and order something and if the address was entered correctly it would be flagged as "in use" and we had to use a different address or deliberately enter a typo.
But that's not true. The problem being described is not caused or implied by a one-to-many relationship between customers and addresses. The actual problem is that the address table has unique constraints defined in such a way that duplicate addresses are not allowed.
So given that the problem was incorrectly defined as one-to-many being the root cause, it makes sense that the proposed solution is many-to-many.
Just to be clear, many-to-many in this use case makes a lot of sense and depending on what the schema looks like it may be the best solution. I am just saying that the problem described was not because of "one-to-many".
For example I work on a multi-tenant web app and we store emails (which have a similar problem with mailing/billing addresses) with a one-to-many relationship to accounts. We don't have a problem with an e-mail being used in several accounts...because our constraints allow it.
Furthermore the unique constraint most likely didn't even serve its purpose: How can you really make sure that an address is unique? There are countless ways to write one and the same address - in its simplest form it might be variations of Street / Str. or Avenue / Ave. Any information combining multiple lengthy strings will be prone to resulting in several slightly different variations.
An example of doing it right: Backerkit (a site for crowdfunding-related surveys and orders) asks for an address every time there's something to send, and only later suggests using a similar previously entered address instead. No "updating", only separate transactions with their respective shipping addresses, all explicitly decided by the customer with some assistance in data entry.
Starting a software project by thinking hard about the data model is always good advice, but even knowing this it is almost always impossible to design things perfectly up front.
You need to hit problems square on in practice, and reshape your model to deal with them. If you think otherwise, if you really think you can think through in advance all the possible unexpected permutations of how your model might not work in practice, like with this address thing, you are just kidding yourself. It never happens that way.
So, maybe the best advice is not only to start by thinking about the data model but to have a mindset of continually thinking about and changing your data model, and to build your software using languages, tools and technologies that better accomodate this.
At least to me considering and planning a lasting data model, despite knowing that it'll change, is a far more significant focus than choosing a framework - a choice which is often dictated by your experience and familiarity with those options anyway. And yet we rarely see discussions about best practices when it comes to data models. Maybe the requirements and solutions are often too specific.
This. People can discuss stacks and tools for hours, whereas the optimal data model seems to be a boring topic, even though very often it turns out far more important in the long run.
Also don't understand why the system for spamming marketing emails needs to share the same address table (and associated headaches) as actual customers. Presumably you will want to spam people who aren't in the system yet...
Note that didn't imply that Linux should designed around a DB. However, with virtualization, the idea of an "OS" may change or vanish. The view is toward "applications", with the associations between applications, users, file systems, and machinery more flexible. In that approach, a DB-centric view may make more sense. A (traditional) "OS" is a machine-centric view.
I think the point is to spend time in designing a good domain model regardless of how it's stored (database).
I believe Ulises is talking about two stages: Top-down a prototype to shake out bad assumptions, then bottom-up main development for a solid foundation.
You can also do that with a good domain model but the rigors of actually having to physically construct it in a database ensures less mistakes.
Now, if you're using a loosy-goosy JSON blog storage solution than there is no advantage to starting at that level.
If my assumptions are wrong, I go back the model and change that and reflow the changes back through the rest of product. It's again usually pretty easy and mechanical to make those changes once the model is again correct.
I cringed on this part, as perhaps an all-too-common software QA scenario:
> It came down to trying to create a fake customer, with a fake order, with a fake item, with a fake item category, with a "paid" invoice, with exceptions sprinkled throughout the codebase to handle all of these special cases and probably more that I no longer remember.
You can do it iteratively, but simply inverting the order is a recipe for a mess of spaghetti that nobody will be able to touch in a just a couple of months.
One, as you mentioned, the database schema seems to be driving the functionality, not the other way around.
Two, the application is a monolith in the worst way possible. Marketing and order entry are two completely different domains. Pigeonholing then into one means you don’t exactly capture the requirements of either domain correctly.
The discussion is a good one to have, but it needs to happen at a level above where it currently is.
If the database is just a detail then you most likely are working on a small toy project. Or a class project. If it is a significant project where performance, scale and security matter, then database is the central concern. You have to start and end with the database. But dealing with data can be boring and cumbersome so we tend to jump to coding and proscrastinate on database and data design.
Also see: https://8thlight.com/blog/uncle-bob/2012/05/15/NODB.html
> Databases and frameworks are details!
Further, you don't know what marketers will dream up in the future and cannot realistically anticipate enough of their harebrained ideas. Try to keep marketing separate from production when possible, but sometimes marketers and/or the bosses want something technically goofy and you have to fudge stuff to get it.
Warn them about possible long-term consequences, but if they insist on Frankenstein, you just have to do it. Get your warnings in writing so that you have a record about their decision when bleep hits the fan later. Further if you avoid complaining a lot in general, then important complaints carry more weight. Otherwise, they'll mistake your important warnings for mundane ones.
When you start a project with a React UI and just throw whatever you need into the schema in order to make things come up on the page...you're headed for disaster.
Or on the flip side...you know how databases work but you don't know how to say NO to features that are expensive.
This seems to be getting worse over time. Databases are becoming a lost art.
I am just barely starting to get grey in my beard (largely from dealing with frighteningly incompetent consulting firms, rather than age...) but I can remember the eldritch incantations dealing with autoexec.bat and DOS memory modes, or the clusterfuck of trying to get printers or new bits of hardware to work, or the panic of trying to fix BSODs when I'd trashed the system installing something dodgy from LimeWire or the shovelware bin at WalMart. The next generation coming through has been shielded from these horrors, and mostly matured in an environment where computers work reliably; and when they do fail, it is usually opaque, inscrutable, and largely hidden from their eyes. Aside from a crash-course in the scientific method for diagnosing and debugging issues, the old dodgy software world exposed one rather harshly to many of the underlying realities of the system, and our current software environments are still mostly built on those foundations, with a few dozen layers of lipstick applied to the pig.
It certainly doesn't help that most instruction in software engineering either hews to the abstract and theoretical or the novel, with passing consideration of the practical realities and the history of the art. Ultimately, we write code that runs on silicon transistors, not ideal Turing machines, and in a great many fields we are retreading extensively explored ground, a hamster wheel of innovation. Every generation seems to have to need to have a go at yet another build system or object database, or rediscover the model-view-controller pattern. We delight in making endless new and exciting and broken wheels, in shameful ignorance of the hard-won lessons of the past.
You know, every problem can be solved with another level of abstraction. No sarcasm, I think it actually holds here.
Good database design upfront, and vigilant upkeep will keep the application layer tidier.
Also, it's a business problem. They can always ask for more cruft and IT has a huge incentive to take the short-sighted route, get promoted and move on.
But create-on-write doesn't solve cross-table relationship flexibility problems: such as changes between 1-to-1 to 1-to-many and/or to many-to-many. If you already have data, that's usually a tricky domain problem regardless of what kind of database technology you use. It's about the semantics of your data, not machines.
Graph databases mostly solve these problem but they have not seen much uptake, don't know why.
Why do the addresses even need to be in the ecommerce system to do the mailing? Couldn't they be in another table? Or a simple CSV? No reason to create fake customer records to send some spam. Something's missing in this story.
But if for some reason they are forced to use the same db, multiple customers sharing the same address doesn't mean they need to share the same row in the table. Keep the 1:many relation without the unique constraint. Customers can then have multiple addresses, even if they're shared with others.
Addresses are complicated too, so you might as well just have a country field + the rest and then use an API to standardize, catching errors and making queries easy.
This, big time. I worked on an ecommerce site that had to ship to Nigeria. No zip codes, often no street name or number. "Two doors north of the post office on the east side of Lagos." The couriers could find it.
Designed as a way to address any location segment with a unique code, based on lat/long, and it's a great way to provide addressing for many areas that don't have formal systems.
This is to help transition areas that are still based on local knowledge to a standard system, without the expense of official street names and building numbers.
But it does. Based on my address, you would know what city and state I am in, and in many cases even which direction in that city my street is (e.g. NW Whatever St). Then you'd know where on the street I am because 3350 is one block over from 2428. You'd also know which side of the street I am because 3350 is across the street from 3347.
If you're comparing address systems by how well local proximity can be inferred from a given address, then Lat/Long is the best system because it's global without language, national or political barriers, doesn't require formal naming, and has no arbitrary designations.
Open Location Codes are based on lat/long, but designed to use alphanumerics to make it easier to remember while also working interchangeably with lat/long and country designations.
Perhaps the developer page will help: https://plus.codes/developers
If you need to mark an area in a reliable, deterministic and politically neutral way, especially in the absence of any existing system, then this is a good option. If you need to drive your jeep to get somewhere, then continue using the lat/long that your GPS or map provides. If you need to translate between the two, then place codes make it easy because they require nothing but simple math.
The relevant section of the definition is short, and an interesting read: https://github.com/google/open-location-code/blob/master/doc...
https://www.usps.com/nationalpremieraccounts/manageprocessan...
Either way, yes you should store the clean standardized data and not the input. I didn't think that was confusing. Also you can get the best of both options with a JSON field.
Have you ever moved into a new house or apartment on a new street or address? Effectively blocking users because you assume their address is corrupt because your system is out of date. At some point you have to choose what is better, trusting that your users are able to enter their address or that an API (from USPS or one of the dozen or so others out there) is able to understand what is and is not a valid address.
And the address lists are updated very quickly now for any decent software. So it's only a problem for a very limited time.
No reason to go to one extreme or another and lose sight of the actual problem.
Come on, this is not about database design. This is about making the wrong technical choice, the definition of "I have a hammer, so everything looks like a nail".
What about export of the addresses, and using a second service on the export?