52

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 ...


41

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 ...


39

Consider what you're trying to achieve. Typically, the Command Query Response Segregation model works well for complex domains. The reason is that you're trying to do one of two things typically: Create/Update/Delete some complex domain entities Run analytic fetch queries (i.e. summation/aggregation queries) Hibernate works well for case 1 allowing you to ...


39

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....


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.


21

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 ...


20

MyBatis is SQL centric. It heps you calling SQL statements and mapping results (tables) to object trees. The main benefit is that it is not an ORM. It does not map tables to object so does not suffer the orm impedance mismatch. Fits well for complex or legacy databases or to use db features like stored procedures, views and so. It is quite simple and easy ...


18

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 ...


15

The usual solution for knowing "which change is correct" is a vector clock. You essentially keep track of counters for each repository that holds the data, and reject changes if a particular client's view of everyone else's state differs from that of the peer it is connecting to. The big question that you have to answer is how you'll resolve rejected saves. ...


15

When people manage data, there are three fundamentally different ways they can add value: Computing Storage and Retrieval Forwarding and Sharing. For computing at the level of simple arithmetic, you can't beat Excel. Even if you are an experienced programmer, you can build a spreadsheet in a fraction of the time you'll take to write and debug a computer ...


11

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 ...


10

This is the Byzantine Generals problem, which is unsolvable. You can never guaranteed synchronize the two servers if you cannot guarantee that at some time in the future, you will have sufficient reliable bandwidth to perform the synchronization all in one go.


8

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 ...


7

This is not originally my idea, but here's a manual way to do it: Create a "UpdateScripts" directory. Create a field in the database which tracks the ID# of the last-run script. Every time any change is made to the database structure, save it as a script in that directory, with a sequential number (such as 00001) in front. Every time the customer launches, ...


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 ...


6

Neither. What does your app need to be able to do? Make sure the hot valve delivers hot water, the cold valve delivers cold water, that the water flows in the first place, that you can extend pipes wherever needed and then worry about implementing actual plumbing to all rooms of the house or what the house will actually look like exactly. The front end is ...


6

I would seriously recomend you keep your images in a folder under the root directory of your web server ("htdocs" or equivalent) and store a reference to the file name in your database. That way you can select the file reference from the database and copy it into an html "img" tag and let your webserver and browser do the heavy lifting. There are several ...


6

This is far from an authoritative answer on using schemas, but generally you want to use them for one of two reasons: Security isolation or portability of related objects. Security It's possible to assign security permissions based on schema, which greatly simplifies complicated structures. Think of an HR system where some people need to see hours, ...


6

In general flagging records as inactive isn't uncommon and would work as you describe it, you often find flags like "deleted" when there is a good reason to keep the information that something existed at some time, for example there are other records that relate to its id. An alternative way would be a date field that is null unless deletion, in case you ...


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

Phew! That's a hum-dinger of a question. One suggestion I'd make is that, should you decide to move to Microsoft SQL Server as your database, then you should really consider using the SO-used library called Dapper. It's a really easy-to-use interface between C# code and SQL Server, and should at least address your statement: It's that bridge between the ...


5

Since the question refers to my comment, here's what I had in mind writing it. First of all, it is derived from the context of your original question. In other circumstances I could give a different advice. The point that made me suggest MyBatis is this: ...we encountered some performance problems. We decided to drop hibernate in favor of plain Jdbc ...


5

You are correct that XE is limited to one database per server. Technically, you should be able to run multiple VMs on your development box with different XE databases installed on each one and connect to those databases from your application. Of course, both databases would be limited by the other restrictions on XE (1 CPU core, 11 GB of data assuming we'...


5

Storing binary image data in a database is not usually the best idea. See this Stack Overflow question. If you are going to have a lot of images it looks like using the filesystem will scale better, but that's something you could benchmark on your own to be sure. I'm also not sure that it is any easier to store the images in the database compared to just ...


5

No, it is not typical that a stored procedure handles all data validation. From my observations, stored procedures should use assertions as a last line of defense against bad data. Like making sure string data doesn't exceed n character length for a particular field. Stuff like the password must meet certain criteria like having at least 1 special character,...


5

I'd like to add some ideas/thoughts to @WalterMitty's nice answer. He's right in that it really depends on the purpose of the Excel files. I would add however that if they contain complex rules AND data, you could think about leveraging the power of rules engines. Rules engines such as Drools, OpenRules or OpenL Tablets allow you to use Excel files for ...


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 ...


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