New answers tagged

0

I'm fairly new to the SQL world as well. Don't let these guys get to you, they are ruthless sometimes (I'm getting a down vote for that!). Gnat is right though when it comes to your issue being a very Large Project. You had better believe that that is by design. Each company wants things done there own way and don't really like the idea of cross-platform. ...


0

If you are going to map from XML to a relational model, you will have to have a one to many relationship from the name to the phone numbers. You will also need a one to many from the name to the email addresses. Entity Relationship Diagram (ERD) Names would need a primary key (designated PK on the image) to be used as a foreign key (designated FK on the ...


0

SQLite has a full text search extension. I suggest you take a look at the documentation. It could optimize the searches because it would create an inverted index that maps from each unique term or word that appears in the dataset to the locations in which it appears within the table contents. Such searches should be quicker than just issuing SQL comparisons ...


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


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


0

I would go for option 1., but I think there is no reason for: a json string which defines the custom fields. Some databases ( eg. PostgreSQL ) have support for traversing JSON, so to get list of keys from a JSON you make: select key from json_each('{"a":"foo", "b":"bar"}'); So in your example you can just select that JSON from answers table ( via ...


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


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

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


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


0

I think you have two choices: either have one profile reference and an indicator in the user record to identify the profile type, or have two profile references and enforce your requirement using insert and update triggers. In the first case, you'll have to forego referential integrity (since your foreign key can reference two different tables), but you save ...


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


0

For your Q1 on where to cut the engineering decisions, how about picking up the unique Id of an entity that would other way give you the required details on the backend? For example, "companies/1/department/1" will have an unique Identifier on its own (or we can have one to represent the same) to give you the hierarchy, you can use that. For your Q3 on PUT ...


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


0

Your colleague is right. Order by the primary key. The primary key is not devoid of meaning. It identifies each row in the table. It is numeric, which implies the order in which records were created. It doesn't fit perfectly into the terminology of your business domain, but don't overlook the simple and obvious solution for ideological reasons, especially ...


2

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


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


0

Not quite database but close enough: Apple's "Time Machine" backup is kind of like a database of all files you ever held. It has the ability to completely erase a file with all versions held, as if it had never existed - very useful to permanently erase sensitive data, or data that was illegal to own in the first place, so you don't want to have any copies ...


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


0

It sounds like an event sourcing approach would be helpful here http://martinfowler.com/eaaDev/EventSourcing.html essentially, each time you update the object, you load it, add the event to the history and save everything. When you save the object, you check to see that nothing else has updated the history in the meantime. If they have, you rebuild the ...


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


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


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


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.


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


0

There are many answers already, but I just wanted to add my summary. Clearly NoSQL concept covers a variety of different approaches in organizing data on-disk, in-memory and exposing it via a query language (some are even SQL-like!). In my view the strength comes from this variety of systems so you can choose the best tool for the job. But still hopefully ...


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


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


0

I've been using couchdb for two years now. It's mostly used for content management and configuration. For hierarchical relationships are much easier to manage when you can visualize them. For read-mostly data, it's easier to edit JSON than it is to write an UPDATE statement in many cases. Doesn't take a programmer, actually, to edit JSON. And SQL gives ...


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


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


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


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


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



Top 50 recent answers are included