Or all of them are wrong. You can't really get to a good model before you have a working software in production and before you learn about many of your broken assumptions.
Top-down or bottom-up, they still don't validate any assumptions. You can spend years on design stage unable to come up with anything that actually works well for the client.
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.
I also always start with the "database" design or the storage model or whatever you want to call it. If you do database design with proper foreign key relationships, etc, the entire rest of the design including the UI and most of the business rules can all flow directly from that.
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.
Eh... I don't know. Each approach has its advantages, but in my experience, I usually have an easier time when I start with the domain model and application logic. More often than not my initial assumptions are wrong and I have to change the model. Easier to change the code than the DB as well as the code. If I hit a weird structural issue than I can usually just rewrite that but in isolation.
I find I can tease out better assumptions and requirements by starting with the model. You immediately need answers to vital questions (more than one address, how are items related, etc). It's so valuable, I can't imagine starting any contentious project without doing that.
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.
Yep, it can go the other way as well. I'm not religious on this one. I know what my experience has been, but those are pretty specific cases. I may change my mind next year, who knows?
"Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won’t usually need your flowcharts; they’ll be obvious.”
As someone with a broad database development background, but with little experience shipping "software" I totally agree with the sentiment of the article. A working prototype can be as simple a handful of shell scripts that write simulation data, and that process it in the form of the primary use-cases. Iterate the core functionality from there before jumping into the more advanced engineering part(?)
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.
Another approach is to start with designing a logical data model first before the database schema or software. This helps avoid the trap of focusing too much on the implementation details of any particular technology.
You start by defining your types (including your database design), proceed to defining your APIs, and only then start writing your software. (Ok, and before any of that you define your problem.)
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.
I came here to say this. This is a case of bad design and it goes beyond database constraints.
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.
> The author advocates starting with database design before software. Some other people advocate the opposite: the database is just a detail.
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.
I watched the clean coder videos a few years ago and Uncle Bob said the exact same thing: the DB is just detail. I am not saying that he was right and I am not going to argue for his approach but it seems some of the big shots of the industry agree that DB is not central to the design.
Same. I spend about 30% of my time writing out the migration and making sure I haven't missed anything. It saves so many headaches later. It's also one of the reasons I stubbornly re-implemented Rail's whitespace formatting of schema.rb into a rake task. I look at that thing 50 times a day. It needs to be readable, I don't care if it clutters up a pull request review or git diff.
I am surprised that their suggested solution was for a many-to-many relationship. Seems like you would want to stay one-to-many but remove the unique validations. If you're sharing address records then it seems there could be many possible frustrating cases. For example:
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.
Sure but now you need to account for orphaned addresses. So in the scenario described you'd either end up accidentally purging the marketing addresses by cleaning up the orphans or you'd send marketing material to old addresses.
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.
> Sure but now you need to account for orphaned addresses. So in the scenario described you'd either end up accidentally purging the marketing addresses by cleaning up the orphans or you'd send marketing material to old addresses.
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.
I am not surprised because the problem wasn't correctly defined:
> 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.
> 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.
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.
In the case of businesses especially, it is often the case that many people have the same (work) address. And, if that business moves, you want all of those people to have the new address, without having to find them all and change all of them. Their work address is the Acme Business Address, id=573, which should be changeable once, rather than finding every record of every person there and changing it. But, the same person could easily have more than one address as well. Many-to-many matches that actual underlying reality, which is what is most likely to work well in the long run.
Example gratuitous complications: I don't want my address updated if my neighbour shares my address and he moves; if my partner breaks up with me on really bad terms and I move I don't want to share my new address. Probably, what's wrong is the concept of people having a relation with addresses while the actual relation is between a shipping and its target address.
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.
Hmmm...I see what you're saying, but if my business moves, and every person who deals with that company has to update their work address, and in the meantime it kept suggesting the old obsolete address that five other people who work there had already corrected, I would not be impressed.
I worked on a codebase with a similar address problem. Oh, and there were no primary keys. Instead, each row referred back to a separate table with four keyed initials.
Good database design upfront, and vigilant upkeep will keep the application layer tidier.
Hmm, we have name (enter as many as you like, Li Pong Tai if you wish), address, country (select, excludes Kosovo and Western Sahara and other territories pretending to be countries), phone and email fields. Email is verified with a code. Forcing a certain format very rigidly is bad practice. Like first name, last name or state, city. What if the customer is Korean or lives on a ranch in the countryside or is a ranger and uses their work address? Or worse, lives in the Medina in Morocco.
In the US there is also a trend for certain companies to use an address "fixer" or "reformater" API to automatically wither check and or fix an entered address. So bad. When I moved into a newly built apartment building a few years ago there were more than a few sites that just would not accept that the actual address was real and valid. ick.
Wrong solution and nothing to do with database design.
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.
> Addresses are complicated too so you might as well just have a country field + the rest and then use an API to standardize, which also makes future 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.
Those, in a way, seem even worse compared to a system based on reference points. Where is G2HR+GW2 in relation to G2HR+GWD? Couriers in locations that use this system would need access to decode that into something that they can use to find a location, and people at locations would need a way to encode their current location. The current zip code system in the US is similar, with the exception of having been in use for about 50 years, and the number being relatively easier to remember.
The whole point of an addressing system is that it is structured. You current home address has nothing to do with reference points either, but it makes it easy to catalog and transfer in a standard format.
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.
> You current home address has nothing to do with reference points either,
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.
Given a complete lack of an address system in rural areas, any structure is better than nothing.
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.
Using normal lat/long to read a map doesn't require you to do a base20 -> base10 conversion, where the characters are 23456789CFGHJMPQRVWX instead of 0123456789ABCDEFGHIJ, and where latitude and longitude are interleaved character by character, in your head.
Again, that's not the point of an address system. You do not use this to navigate (even with proximity built-in), but to transfer location data in a compact format. I don't see why this keeps getting misunderstood.
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.
0 and 1 look like I and O, B looks like 8, and so on. They picked characters that can't be easily confused. The letters are also chosen so they won't spell meaningful words.
Most US sellers use some sort of USPS certified address validation and cleanup software to catch data entry errors and prevent delivery problems. So dumping everything but the country into a single big field isn't necessarily a viable design.
That's why I said "use an API to standardize". I don't see why this is incompatible with having a single field. Unless you actually need the component parts, it's much easier to manage than the schema needed for all the variations.
It's not really practical to call the API every time you need a normalized address. Normalize once and ask for user confirmation, then store the clean address with a separate field per component.
Sure, that's covered by "Unless you actually need the component parts"
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.
Don't trust your users. Use the address validation software, but if the address doesn't work allow the user to specifically choose to use their original entry. That's how most large ecommerce vendors operate now and it allows a manual override for new addresses.
And the address lists are updated very quickly now for any decent software. So it's only a problem for a very limited time.
Not just new users, due to some bitrot Pizza Hut has decided that my house, built in the 60's and probably occupied for 100+ years, no longer exists and they won't let me order. Not a big loss in this case, but imagine if this was someone like amazon deciding my address wasn't valid.
Ok, have 2 fields. Input and Standardized. Confirm with the user if their input is not being standardized to make sure it isn't an error (which is explicitly called out in this article as a problem).
No reason to go to one extreme or another and lose sight of the actual problem.
What they're really asking for is multi-user accounts, which is a whole can of worms... also the address uniqueness requirement seems like it'll leak your customers' information.
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...
That's a huge problem with SQL in general. It ought to be trivial to maintain multiple views of the data to allow you to migrate old clients bit by bit, but in practice it's rarely done.
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.
Re: "[flexible schema change is a] huge problem with SQL in general." -- I've been a proponent of the idea of "dynamic relational". You can add any column on whim to a row just by including it in an INSERT or UPDATE command, unless an explicit constraint/rule forbid it for a given table or database. You could even make tables similarly create-on-write. No DBA's needed! (within reason) Comparisons have to be done with care, though, as with any dynamic/implied typed system.
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.
Using "Dynamic Relational" (DR) is much more similar to existing RDBMS. You don't have to relearn much. I'm not necessarily saying DR is objectively better or worse than graph DB's, only that DR is a smaller learning curve to those using the most common RDBMS out there.
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?
I've often found the "proper" database design is too confusing to users and/or managers. Users will have to understand the complex relationships to enter, inspect, and trouble-shoot data properly. Often they don't. Simplicity often trumps logic and the D.R.Y. Principle even if the limits (simplicity) create extra work in some situations.
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.
The db is not only about tables, there's a reason why views exist, the users/managers should only query views, these are easy to change whenever the underlying private schema changes.
Views are an imperfect abstraction (as usually implemented). Plus, pre-many-to-many-ing all relationships will make for a notably slower system even if views hide many-to-many-ness. (As I mentioned elsewhere, one often can't know which relationships will grow to many-to-many over time.)
This is why I believe the future of source code management will be via relational databases instead of in hierarchical file systems. Large projects are too involved for trees. The same with GUI specifications: They are too complex to manage with OOP/OOM. Plus, a GUI spec in a database is (mostly) language-neutral so that you are not tied to a GUI library written in a specific programming language. You learn one engine, and then use it for PHP, C#, Java, Python, etc.
Please elaborate. Non-trivial data structured will either need a database, or end up resembling one anyhow when you add features they'll eventually need as they mature (concurrency, data integrity, querying, adding indexes without changing functionality/design, backups, etc.)
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.
Data-structures are machine-centric, not abstract relational algebra. You can understand why it's important to think about data structures for the machine. Which is a completely opposite view from relational databases, where you are supposed to not think of how data relates to the machine at all.
In practice the boundary between both is fuzzy. Processing DB data can and is often done in a row-by-row or cell-by-cell basis. Not all computations are convenient to reduce to relational algebra, at least for most practitioners. We can't all be Sheldons.
Projects and even companies I have witnessed dying are more often than not done in by underinvesting in databases.
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.
There is a lot of hard-won knowledge that is aging out of the profession increasingly lately, I have been observing. It's tempting to blame web development, and its proverbial low bar to entry, but I think the real problem is computer UX getting too good, such that you can accomplish almost anything one would desire with a computer, without having to delve into the nasty implementation details.
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.
This is why I don't think of database as an interface. Instead, create functional interface with some business meaning (REST API, GraphQL, a Java interface - whatever suits a particular use case), which will be versioned and properly maintained with backward compatibility where possible (which is, I think, almost always possible). It doesn't cancel a proper DB design though, but helps a lot when you need to change it. Or move to another DB engine altogether.
You know, every problem can be solved with another level of abstraction. No sarcasm, I think it actually holds here.
While a many-to-many DB relationship can in theory model a 1-to-many relationship via constraints and/or domain logic coding, it seems kind of anti-YAGNI to me. Being that ANY 1-to-many relationship can potentially change into being many-to-many, which ones do you "pre many" and which ones are left 1-to-many? My crystal ball is not that powerful. If it were, I'd be competing with Warren Buffett instead of puzzling over database design.
This isn't about database design, per se, it's about data model design in general.
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.
Yeah, I think you're correct to point out the tension between designing up front and adapting to reality you learn as you go. If you haven't been exposed already, the way Datomic approaches this problem has been eye-opening for me: https://docs.datomic.com/on-prem/best-practices.html#plan-fo...
I agree and I'm surprised potential data models aren't as often discussed and debated as the choice of technology and stacks.
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.
> I agree and I'm surprised potential data models aren't as often discussed and debated as the choice of technology and stacks.
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.
It might have helped to model an account system where customers belonged to accounts and addresses belonged to accounts. Customers can then have multiple addresses by their association to the account and the unique constraint (if required) could have been applied on the account<->address relationship.
84 comments
[ 4.8 ms ] story [ 158 ms ] threadI 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!
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.
Good database design upfront, and vigilant upkeep will keep the application layer tidier.
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.
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...
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.
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?
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.
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.
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.
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.