Death by Database

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&#x27;re sharing address records then it seems there could be many possible frustrating cases. For example:<p>What if someone goes to create an address, typos it, matches another customer&#x27;s address, and then fixes it updating the other customer&#x27;s address in the process? The other customer might accidentally send their luxury item to a stranger if they aren&#x27;t paying attention during checkout.<p>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&#x27;s address as well. Same as the other scenario, you might end up with a customer sending their item the wrong place.<p>These might be edge cases but in general I think it&#x27;s dangerous to change things like an address without an explicit action from the user.

Addresses is one of those categories for which people should regularly read the &quot;falsehoods programmers believe about X&quot; list. Here&#x27;s one: <a href="https:&#x2F;&#x2F;www.mjt.me.uk&#x2F;posts&#x2F;falsehoods-programmers-believe-about-addresses&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.mjt.me.uk&#x2F;posts&#x2F;falsehoods-programmers-believe-a...</a>

This isn&#x27;t about database design, per se, it&#x27;s about data model design in general.<p>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.<p>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.<p>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.

What they&#x27;re really asking for is multi-user accounts, which is a whole can of worms... also the address uniqueness requirement seems like it&#x27;ll leak your customers&#x27; information.<p>Also don&#x27;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&#x27;t in the system yet...

That Linus Torvald quote comes to mind: “Bad programmers worry about the code. Good programmers worry about data structures and their relationships.”

The author advocates starting with database design before software. Some other people advocate the opposite: the database is just a detail.<p>I think the point is to spend time in designing a good domain model regardless of how it&#x27;s stored (database).

I&#x27;ve often found the &quot;proper&quot; database design is too confusing to users and&#x2F;or managers. Users will have to understand the complex relationships to enter, inspect, and trouble-shoot data properly. Often they don&#x27;t. Simplicity often trumps logic and the D.R.Y. Principle even if the limits (simplicity) create extra work in some situations.<p>Further, you don&#x27;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&#x2F;or the bosses want something technically goofy and you have to fudge stuff to get it.<p>Warn them about possible long-term consequences, but if they <i>insist</i> 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&#x27;ll mistake your important warnings for mundane ones.

Projects and even companies I have witnessed dying are more often than not done in by underinvesting in databases.<p>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&#x27;re headed for disaster.<p>Or on the flip side...you know how databases work but you don&#x27;t know how to say NO to features that are expensive.<p>This seems to be getting worse over time. Databases are becoming a lost art.

This is why I don&#x27;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 <i></i>versioned <i></i> and properly maintained with <i></i>backward compatibility<i></i> where possible (which is, I think, almost always possible). It doesn&#x27;t cancel a proper DB design though, but helps a lot when you need to change it. Or move to another DB engine altogether.<p>You know, every problem can be solved with another level of abstraction. No sarcasm, I think it actually holds here.

Same. I spend about 30% of my time writing out the migration and making sure I haven&#x27;t missed anything. It saves so many headaches later. It&#x27;s also one of the reasons I stubbornly re-implemented Rail&#x27;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&#x27;t care if it clutters up a pull request review or git diff.

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.<p>Good database design upfront, and vigilant upkeep will keep the application layer tidier.

The real problem here seems to be that changing the schema seems prohibitive expensive.

Wrong solution and nothing to do with database design.<p>Why do the addresses even need to be in the ecommerce system to do the mailing? Couldn&#x27;t they be in another table? Or a simple CSV? No reason to create fake customer records to send some spam. Something&#x27;s missing in this story.<p>But if for some reason they are forced to use the same db, multiple customers sharing the same address doesn&#x27;t mean they need to share the same row in the table. Keep the 1:many relation <i>without the unique constraint</i>. Customers can then have multiple addresses, even if they&#x27;re shared with others.<p>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.

Very shallow post.<p>Come on, this is not about database design. This is about making the wrong technical choice, the definition of &quot;I have a hammer, so everything looks like a nail&quot;.<p>What about export of the addresses, and using a second service on the export?