Hot answers tagged

67

The performance argument is usually the one which is most intuitive. You especially want to point out how it will be difficult to add good indexes in an incorrectly normalized database (note: there are edge-cases where denormalization can in fact improve performance, but when you are both inexperienced with relational databases you will likely not easily see ...


23

I'll be having to implement a database with my boss ... Using dedicated Database Management software might be considerably easier (sorry; couldn't resist). lngStoreID | vrStoreName | lngCompanyID | vrCompanyName | lngProductID | vrProductName If this database only cares about "logging" which product was sold where, when and by whom, then you ...


14

Why does a good relational schema improve a database? The answer is: it doesn't always improve a database. You should be aware that the what you were likely taught is called Third Normal Form. Other forms are valid in some situations, which is key to answering your question. Your example looks like First Normal Form, if that helps you feel better about ...


10

There a multiple reasons why using one large "god table" is bad. I'll try and illustrate the problems with a made up example database. Let's assume you are trying to model sporting events. We will say you want to model games and the teams playing in those games. A design with multiple tables might look like this (this is very simplistic on purpose so ...


8

Perhaps it is too naive, but did you consider making a fresh database for your fresh application and writing some converter from the old (badly designed) database to the new one? That converter would be hard to code, but you'll get some more cleaner data from it.


7

It depends. If you know you will need all the data eventually, it is technically faster to download it all at once. However, if you can download a portion of the data and use it to keep the user busy (rather than making him wait for the last byte to arrive) while you download the rest either in the background or on an as-needed basis, your application ...


5

So, If I create a single table called thread, and add a new record everytime a new thread is created, and associate it with a posts table such that all posts belonging to that thread use it's ID as foreign key, I believe the database will bend down on it's knees in a matter of weeks. I fail to see the database problem here. 100s of new threads ...


5

How should I deal with this legacy data that "breaks the rules". So the starting point is to talk to your domain experts. What do they do with the "invalid" data in the legacy system today? Also, is the legacy system the book of record? or does it describe entities that are actually controlled somewhere else? A mix of both? You may need to consider ...


5

In general, when used database connections are local variables, since they are almost always retrieved from some factory to help abstract away implementation details. Pragmatically, those details matter since you almost always want to pool your database connections, and the factory handles all of that. The factory in turn usually is an instance variable ...


5

I think there are at least two parts to your question: 1. Why shouldn't entities of different types be stored in the same table? The most important answers here are code readability and speed. A SELECT name FROM companies WHERE id = ? is just so much more readable than a SELECT companyName FROM masterTable WHERE companyId = ? and you are less likely to ...


5

Quote of the day: "Theory and practice should be the same... in theory" Denormalized table Your unique hold-it-all table contains redundant data has one advantage: it makes reporting on its lines very simple to code and fast to execute because you don't have to do any joins. But this at a high cost: It holds redundant copies of relations (e.g. ...


3

TL;DR - They're designing the database based on how they were taught when they were in school. I could have written this question 10 years ago. It took me some time to understand why my predecessors designed their databases the way they did. You're working with someone that either: Gained most of their database design skills using Excel as a database or ...


2

You can do better than either XML or JSON by exploiting the particular characteristics of your particular data. If the data is completely flat and every row contains the same fields, then CSV is going to be more efficient than either XML or JSON. The reason people generally prefer formats like XML and JSON is because they recognise that the life-time cost of ...


2

This is essentially how many compression algorithms work (if you're interested, here's a detailed explanation of GZIP), which is also why are they so efficient when compressing text in general and XML specifically. If I were you, I would start by asking myself the following questions: Is the size of data actually important? At $0.0300 per GB for an ...


2

If you are talking about document stores, and you have your song database, try think about storing the lyrics of all the songs. Those can't really be stored in a relational way. Because you can't really model the words of a lyric in a relational way to the song. What you can do however is put all the lyrics in a document store and make them search-able. ...


2

I would recommend an ORM-System. Because you tagged your question with C# I'd say a good variant would be using Entity Framework with a Code First approach. This will allow you to change your code and automatically apply the changes to the Database. Depending on your configuration you can allow the Database to automatically update the Db-Model or to invoke ...


2

Database systems can implement very different approaches. Just compare SQL vs. NoSQL. And for the latter compare key-value stores with graph databases. So you'll not find a silver bullet that will offer a single API with all the benefits of these databases. So you'll have to narrow down your scope. Considering the limits, filtering, ordering, many of ...


1

You need a data-cleansing exercise. You don't have a chance in hell of writing decent code that will work if the existing data breaks the rules you're trying to establish. Your code is not at fault if it enforces rules on new data, but can't handle old data that - from the sound of it - wouldn't know a rule if one walked up to it and said hello. Your code ...


1

But it's very tedious... Tedious, perhaps, but the updates is then under your [code's] control and can be properly Transactional, unlike "random" changes popping down to the database as and when any item changes.


1

First, lets make a clear statement about performance: in 99,99% of all real world cases the performance will be literally the same, so - as usual - performance is not a useful criterion for making such a decision. The primary questions you should ask yourself here is: "can I imagine a situation where I need different database connections for different ...


1

With NoSQL/Document databases, you have to think about how you're going to be querying your data. Think of it like building indexes except the indexes are your data. You can have multiple indexes that can have duplicated data. The RDBMS gives you the luxury of maintaining all the various relations, constraints and indexes so you can have the best structure ...


1

The most efficient would probably be a binary format which you could read directly into memory, skipping the parsing step.



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