Hot answers tagged

38

Generally speaking, if your workflow is a perfect match for relational database queries, you'll find relational databases to be the most efficient approach. Its kind of tautological, but its true. The claim that many NoSQL advocates would make is that many workflows were actually massaged into a relational form, and would have been more effective before ...


31

While I agree with your premise that NoSQL is not a panacea for all database woes, I think you misunderstand one key point. In NoSQL database you have only one criterion you can search for effectively - the key. This is clearly not true. For example MongoDB supports indices. (from https://docs.mongodb.org/v3.0/core/indexes-introduction/) Indexes ...


15

NoSQL is a rather vague term, since it basically covers all database systems which are not relational. What you describe is a key-value store, which is a kind of database where a blob of data is stored under a key, and can be quickly looked up if you know the key. These databases are blazingly fast if you know the exact key, but as you say yourself, if you ...


8

Your assertions about relational databases are all true, up until the point where you have so much data you can't fit a copy of it on a single server anymore. Then you start running into all sorts of interesting problems. How do you split up your tables so most of your queries can run on a single server? How many copies of the data do you make? How do ...


6

It is not unusual for the database to be on one server and the scripts to be on another server. In this situation, it is necessary for the script to know the name of the server that holds the database. This name is sometimes known as the "database host name" because it is the name of the server that hosts the database. Localhost is a special name that ...


5

Data Flow Diagrams sound like what you need From Wikipedia: A data flow diagram (DFD) is a graphical representation of the "flow" of data through an information system, modelling its process aspects. A DFD is often used as a preliminary step to create an overview of the system, which can later be elaborated.2 DFDs can also be used for the visualization ...


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

NoSQL databases have very little to do with “No SQL”. They are about admitting that you can’t have a database at scale that is always consistent and supports complex transactions and has durability. In a normal relational database all indexes are automatically kept updated within the scope of a transaction, so can be used for any query. In a NoSQL ...


5

I think you have complexity because you are starting with over-complication: Paths would be something as: companies/1/departments/1/employees/1/courses/1 companies/1/offices/1/employees/1/courses/1 Instead I would introduce simpler URL scheme like this: GET companies/ Returns a list of companies, for each company return short ...


4

IMHO, I think you're missing the point. First, the REST API and DB performance are unrelated. The REST API is just an interface, it does not define at all how you do stuff under the hood. You can map it to any DB structure you like behind it. Therefore: design you API so that it's easy for the user design your database so that's it can scale reasonably: ...


4

The names of your tables should be BUSINESSES, TREATMENTS, CLIENTS, EMPLOYEES, APPOINTMENTS. Nobody appends "_TABLE" to their table names, just as nobody appends "_Word_Document" to their word document names. Also, we usually do not include joined fields in table designs. The fact that these fields are available is evident by the presence of the foreign ...


4

It's not necessarily as bad as you think and may even be the most optimal representation if you have an index on parent_id. Let's say you're interested in the descendants of B above. In that case, you would first query for nodes which have parent_id matching the ID of B. That gives you D,E. Now query for nodes which have the parent ID of either of these. ...


4

I'm not sure whether hotel services are "booked with the room". What about conference rooms or all-day pool passes? Hotels have customers that are not guests, so a "CUSTOMER" entity is a better suit. A customer that is staying in a room is a guest. Also, entities/tables should have singular names. EDIT: There's absolutelly no problem in what you call ...


4

Why do you need a triple reference? The savoy is a HOTEL. "Turkish massage" is a SERVICE. If the Savoy offers that, that's an entry ii your H_S table. If someone stays at the savoy, that's an entry in the BOOKING table pointing to the hotel; if they order a massage as well, that's an entry in the B_S table pointing to the booking and to the service. ...


3

Relational Databases are optimized to search for any value in the datarow effectively. Don't confuse the ability to search on "any" value in a row with "every" value in a row. The most effective way to do this requires one or more indexes. You could have indexes include all the fields, but then you just hindered you're ability to make changes that ...


3

My suggestion is similar to #3 but with a difference. While in #3 su suggest not deleting the video at all but moving it to another user who will be the owner of all orphaned videos, I suggest this: A special video called "deleted video" should exist, which owner is "Deleted video owner". That special video's ID is, say, 1. As PLAYLIST_ITEM is a ...


2

You should consider the idea that specifications change. Today there are 50 default fields, tomorrow there could be 51. You do not want to be adding a column every time the requirements change. The reverse might also be true; what if a column is no longer mandatory or is no longer required? The sample ERD below allows the following: people can take a ...


2

In general, you don't want any implementation details exposed in the API. msw and VoiceofUnreason's answers are both communicating that, so it's important to pick up on. Keep in mind the principle of least astonishment, especially since you're worried about idempotence. Take a look at some of the comments in the article you posted ...


2

Question 1: Is my thinking correct, is "where to cut the hierarchy" a typical engineering decission I need to make? Maybe - I'd be worried that you are going about it backwards, though. So ok, when returning a company, I obviously don't return the whole hierarchy I don't think that's obvious at all. You should be returning representation(s) of ...


2

Usually a 'secondary index' is an index which is not used to enforce the primary key constraint. This seems to be a pretty widely-accepted term in the world of relational DBMSes. In non-relational world, I don't know.


2

With an index on parent_id, I actually think this is a good way to organize the relationship, and we have been using it that way in many places. Also, I see no obvious better way, as the relationship is 1:N, so if you try to store parent->child, you need an extra table, and you end up with the same logic again. The core point is having the index on ...


2

You shouldn't split a normalized table just because it have "many" fields. It will not make anything easier.


1

Use numeric status values where the higher value represents the higher state. Add that to your index in descending order. Then pull back the result ordered by time stamp and the status in descending order. This way the entry with the farthest progression is always returned.


1

A better approach would be two keep two tables, current and history. The current table would always be the latest update. The other table would be a history table, which would always be just inserts and reads. If you want to get the latest record, query the current table. If your not allowing any dirty reads it will always be the latest record. If you ...


1

First you need to decide how to make this change. Either update all the necessary invoice date fields or cancel the invoice and copy/rebuild all the data with a different date and possibly invoice number. Once you can code making this change, you can then decide on how to apply it to many invoices. If you think the problem is going to continue to stem from ...



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