61

(it's "node-oriented", if that even exists) Start here. When dealing with a complex application like a database (even a simple database is a complex application), you should be familiar with the history of the domain and the proper terminology and have at least a very high level idea of the architecture. You could start from the Wikipedia article on ...


51

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


38

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


38

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.


20

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


19

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


17

You are overnormalizing (and as a database specialist, I don't say that often). Think of how you use this data and you will see it makes the most sense to be in one table. If each address related to multiple zip codes, a second table makes sense. Since the relationship is one-to-one there is no need at all to split out the table unless you have too wide a ...


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


14

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


13

I'd suggest having one table for tags, one table for "audios", and a third table to keep track of which tag is associated with which audio. Something like: tags_audios audio_id | tag_id ---------------------------- 1 | 2 1 | 3 2 | 1 2 | 3 3 | 6 This is usually how I see m:n relationships stored, and it is ...


12

Is this good practice? It's called Object-Relational Mapping. ORM. It's done all the time. Am I better off making one big call to the database and populating my objects and working with these objects, or should I retrieve data from the db only when needed? That's imponderable. First, you haven't defined "better". Second, it depends on the ...


10

The right answer is probably "both". This fits into the production/staging/development separation used in many development cycles. Development/testing servers are available, as needed, to developers so that they can design new features and do regression testing with total freedom and impunity; nothing can break so bad that you can't just start over and ...


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.


9

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


8

Each developer should have their own copy of the database for development. Period. Anything else gets in the way and can cause obstacles in the development environment. IMO, there is nothing worse than chasing down a bug for hours that was caused by a change somebody else made to some data you are using in your debugging. As far as this being more work for ...


8

I would say you could very easily justify putting the logic in the database layer. It really is about encapsulation. If you are calling stored procedures to access your data then you are encapsulating that business logic the same as if you had an ORM layer. I think people sometimes get bent about scalability and portability when you really don't have any ...


8

Yes, a hard problem, easily underestimated. And could be a lot of work. If you are on Microsoft technologies, you may want to have a look at Microsoft Sync Framework here and here.


7

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


7

Such a table is often referred to as a link table or bridge table. It is the standard way of creating a many-to-many relationship. With a direct foreign key between the two tables, you can only create a one-to-many relationship (because the primary key that the foreign key points at is also a unique constraint), or a one-to-one relationship (if the foreign ...


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

Having read both questions, I think we may have all missed one critical point. The correct answer may depend on the type of software you are developing. The DBA group tends in large part to work on business critical Enterprise software systems and their answers tend to reflect what is necessary in that world. There is a huge difference in what is needed for ...


6

"(it's "node-oriented", if that even exists)". - This may be why you are not finding much! Dive in with version 0.1 and see where you get. You may learn more from trying to produce what you want that from asking what you "should" do. Give it a few days and then review where you've got. About 18 years ago I wrote a basic database system (for fun, go figure) ...


6

I would personally do it the following way: The applications released to your clients must have clear version numbers, for example "1.2", "1.4" etc, which is easy to be quickly determined on the client machine (registry/configuration file/table). The database schema for every single version is known and constant on every client machine. When you roll-out a ...


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