54

Google has developed a library that helps validate postal addresses for every country in the world, which you can use to design a schema to store this data. Look for the most common required fields across addresses from your targeted customer base to get started, and as you identify further countries with different requirements you can continue to adjust ...


42

The universal way to store a geographical address/location in a database is this one: [Address] nvarchar(max) not null This requires the least amount of programming code (and so cuts maintenance costs) and is fully compatible with any address. It has, however, three big issues: The lack of data validation means that the field can be used for the purposes ...


41

These days, you are likely to see reads (queries) handled differently than writes (commands). In a system with a complicated query, the query itself is unlikely to pass through the domain model (which is primarily responsible for maintaining the consistency of writes). You are absolutely right that we should render unto SQL that which is SQL. So we'll ...


37

There isn't one. Every country has different address formats. If you are lucky, and they have a format at all! Obviously latitude/longitude will give you a point on the globe, but it's not really useful for identifying individual houses. Just consider a tower block for example. Your best bet is to check each countries postal service for an official format....


23

As I understand it, a main point is to split the Domain Logic (Business Logic) from the Infrastructure (DB, File System, etc.). This is the foundation of the misunderstanding: the purpose of DDD isn't to separate things along a hard line like "this is in the SQL server, so must not be BL", the purpose of DDD is to separate domains and create barriers ...


21

The only universal format is to have a single text field which may have multiple lines of text. This will allow any possible address on earth.


19

If you've ever been on a project where the organization paying to host the application decides that the database layer licenses are too expensive, you'll appreciate the ease of which you can migrate your database/data storage. All things considered, while this does happen, it doesn't happen often. You can get the best of both worlds so to speak. If you ...


13

I have been developing software solutions to be used in many countries. We address this issue by starting with the larger entity first, i.e. country then have fields down to the least common or smallest. It works well for all countries we have experimented with so far. We also have a smart duplicate prevention system, and merger for those that have somehow ...


9

The set manipulation logic that SQL is good at can be integrated with DDD no problem. Say for example I need to know some aggregate value, total count of product by type. Easy to run in sql, but slow if I load every product into memory and add them all up. I simply introduce a new Domain object, ProductInventory { ProductType TotalCount ...


8

There is absolutely nothing wrong with a table with one row. If this is part of your design, you should enforce it. Give the table an identity column, give it a uniqueness constraint, then add a column constraint to only allow a single value. This will ensure that nobody will be able to add a second row, which could be catastrophic if the SQL statements ...


7

Storing things that are logically two different things as one thing always means that you have to perform extra work when you actually want to treat them as different things. That seems like wasted effort with no gain - two table columns do the job perfectly. You can still retrieve both simultaneously with the appropriate SELECT statement, or only one of ...


7

Video distribution sites like YouTube are read heavy and bandwidth heavy data users, but in terms of complexity, they're relatively simple to scale. They're essentially a CDN type scaling where you can easily replicate the data in widely distributed caches close to the user. Only the first few downloads of new videos requires hitting the main database, but ...


6

What you want to do is "freeze" the information at the time of an order. When a client makes an order you just make a copy of the description, price, product etc. in the order record. For instance, the price of a product can change a lot. You don't want a new product record for every price change. So you "freeze" the price in the order record. Once an ...


6

I believe most of the "modern" RDBMS implementations are based on the Cascades optimization framework. I shall talk about how Microsoft SQL Server handles this, as that is the DBMS with which I am most familiar. SQL Server is an implementation of the Cascades optimization framework, so it's workings and the ones for other "modern" RDBMS should be similar. ...


5

The log is an append-only data structure, which is much easier to ensure the it is either in its old state, or its new state. Whereas the transaction data pages are all over the place. So there are two differences: (1) the 10MB written to the log could possibly happen in one write, whereas the 10MB written to the data pages may take numerous I/Os to ...


5

You should look into database migrations, it's a common strategy used on databases. Basically you associate your codebase with a database schema version. To keep your schema up to date you maintain a chain of incremental change scripts each carrying a sequential version number. This has the added advantage that you can use the scripts to upgrade an ...


5

Many databases have a "dump" feature that creates a text-based representation of the database. MySQL has mysqldump, for example. Depending on your database, you can specify to only dump structures and procedures, and leave behind the data. Or you might be able to selectively dump some reference data. If your database has this feature, there's a pretty good ...


5

The API should completely hide the database. Regardless of SQL vs No-SQL approaches. That means that: The DB should be filled via calls to the API. Callers of the API don't have to know what the database implementation is Which 'team' is responsible really depends on your company makeup. But the design allows the API developers (perhaps this team includes ...


4

I guess there is no standard way to do it, each system uses their own policies for the conflict resolution. I made some simulations using two devices, computer and phone, and Google Spreadsheet to check how Google Docs handles conflicts automatically. Here some cases: Case 1 Computer and phone are offline Computer edit cell with value 'computer' and after ...


4

You would enter that fact by first creating (or looking up) a record in Date to represent Mar 2, 2013, then create a new record in Rental with three foreign keys. (You still need the table Rental, otherwise you can't represent arbitrary re-rentings of a customer or a car - or on the same date.) However, that is surely unnecessary. A date is one scalar piece ...


4

It's not clear to me that you have a problem that you need to solve. A single-row table is in no way a problem for the database itself, and allows you to apply data types and constraints to the data. I'm not sure that you are trying to solve a real problem, to be honest.


4

Well, you wrote When i setup my integration tests a while back i took a SQL dump from production db and converted that to HSQL (DDL & inserts). so automate this process - build some scripts or program which does what you did with as few manual steps as possible, then you can repeat the process of updating your test database much easier and as often ...


4

Orders/Receipts Are a separate data structure to the product catalogue/shopping cart. The catalogue/shopping cart dynamically update. If the price goes up, so does the price of the item in the cart. When you go to checkout, the shopping cart is turned into an Order. At this point the prices are discounted and fixed, units are pre-allocated from stock, a ...


4

This is a very broad question with a very broad answer. There are a lot of things involved. The examples you mentioned are distributed systems. The characteristics you mention are obtained by scaling the systems horizontally instead of vertical as it tended to be some time ago. See for example this post on database scaling. And there is also not a specific ...


3

To me, there's no question. Go with Option 1. It makes sense that you'll have Products and Categories. They're different, and shouldn't be forced to live in the same table. However, a productName (reasonably shortened to 'name') is not the same as a categoryName (also reasonably shortened to 'name'). You can use context to figure out which 'name' you ...


3

Sometimes you have a corporate environment where development is client-side only, and databases are the realm of the DBAs. In these cases, devs will still have a common development instance to work on even if they cannot make changes to it, they can still fill it with crap data for testing. If your DB is a mainframe or minicomputer, then chances are the API ...


3

Why not! Personally, I prefer to have scripts that populate an empty database with new data so I can a) know exactly what is going in and b) to easily change them. I do this even for our big production databases, keeping a script with the 'usual data' that I would otherwise have to enter by hand - things like a predefined user id for myself and ...


3

The design of an ACID database is significantly different from that of a BASE database. ACID gives you assurances that a transaction will be complete. Distributing an ACID database adds significant complexity. BASE and other non-ACID databases are simpler to build and test. Concepts to test (choose according to ACID / BASE design): Is is possible to ...


3

It seems to me that you're using a database for something that should actually be stored as a file. Your description reminds me a lot of Wiki pages, where users can edit the content. In Wikis, or at least in the implementations I've seen, the pages are persisted as files. This helps you with other web details such as allowing your users to cache your ...


3

This is a situation where things have gone wrong for a lot of developers over the time. In a perfect world it would just be to inject the chosen connection everywhere a connection is needed. But SQL isn't just SQL. There are so many minor differences between to different database providers. This means that you most often will need different SQL statements ...


Only top voted, non community-wiki answers of a minimum length are eligible