Tag Info

New answers tagged

-1

SQL is the query language used in all Relational Database Management Systems. Different systems often have different extensions to the SQL language, but there is a standardized core which is the same across all. The extended version of SQL used in Microsoft SQL server is called T-SQL or Transact-SQL. It is a superset of standard SQL: In addition to the ...


0

There should not be any significant performance impact A clustered index is basically free - why not use it? Insert check for unique is an index seek It is very very fast for single or composite key Even if there performance implication for me data integrity wins every time If the data has a natural composite key then you should use a composite key ...


0

Using a hash is a good idea. Since security is not the goal in this case, choose a hash function that is fast (md5 is fine). Unless you plan to split the hash calculation across multiple threads/processes, you don't really need to store the current hash value in the database. If your process is a single script, you will just have the current hash in memory, ...


2

The data sync would be much better and faster, if it can be done on the basis of some kind of delta identifier or flag. Basically, you should update the target db data rows only when it is out of sync with the source db. In SQL server db, you can take the help of the Checksum fn also to build the delta based identifier. You should develop a SQL based job ...


2

The scenario itself: "a user views a video should increase view count" is a client side activity resulting in backend data changes. It is initiated by the user using the client app, so it is okay to put the code that initiates and increases of view count in the client app. However, here are some other/different ideas to consider: Keep using the client ...


0

This is going to depend on what language you're using, but you should have one configuration for debug and one for production. You can either focus on ensuring the wrong config is never deployed with your project, you can never run the project on the same server as the database is on (assuming you have disabled remote DB connections), or you can make sure ...


1

A number of options: Using firewall, block anything from accessing production database except a few servers that are allowed. This should be done always in production. Define production configuration on the production server and don't check it in source control. Source control should maintain only development configuration file with the development ...


0

You've got a holiday and the basic information about it. Holiday: holidayId (pk) name What types of holidays do we have? There are the fixed holidays, the moving holidays and the "I'm not even going to bother calculating" holidays (Easter, I'm looking at you). For this, you would have subtables. There are the fixed, repeating holidays. Things like ...


0

I have figured that it's best to store the day count which the event/holiday is on. I had a look at the Queens birthday in Australia and its every 2nd Monday of June, it doesn't move on to the next month. For most of Australia accept Western Australia that's the queen's birthday. in western Australia the queen's birth day is on every 5th Monday of September, ...


2

What you have is not flexible enough. The basic requires are you need to be able to express e.g. the following: U.S. Memorial Day is the last Monday of May. U.S. Thanksgiving is the fourth Thursday of November. Christmas is the 25th day of December. Easter is... maybe just scrape Wikipedia? One simple way to express this is to add fields for the ...


2

Joins are not slow. They are incredible fast if you join to a primary key or indexed column. You should not make design decision from the assumption that joining is a problem. Now, there may be particular cases e.g. with very large datasets or distributed databases, where joining may be a performance problem, and there are various ways to mitigate that ...


0

In the bigger view, this sounds like an Anemic domain model (Martin Fowler - https://en.wikipedia.org/wiki/Anemic_domain_model) These Data Access Objects are a direct mapping to the database and do not reflect your business logic. The way to go would probably be a CQRS (https://en.wikipedia.org/wiki/Command–query_separation) model, but it would probably ...


0

I would go even further: User: Id, Name, WallId Post: Id, Content, Title, WallId etc... Wall: WallId Group: Id, Name, WallId... Basically, post belongs to a Wall. Wall can belong to a user or a group.


2

Any thing ("Entity") that can exist on its own, independently of anything else, should have its own table. User: id, name, hashed_password, join_date, birth_date Group: id, name Relationships between things require generally require "linking" tables. Post: id, user_id, group_id, post_date, post_title, post_content The key to success is proper ...


0

You would probably want some layer(Facade pattern may be) that would expose only the necessary fields. Sounds like right now you are exposing your ORM models to all the layers and creating a spaghetti/unsecure code :) Start by defining clear responsibilities of each layer and only exposing the model data that is necessary for the layers above. If it ...


5

You are suggesting a client-side join: Fetch some data from the database via a query, and then in code you perform further joins via lookup tables loaded from files. This is almost certainly going to be slower, and definitely more complex than just doing the join in the database. Think about it - why should you be able to write you own join-logic to be ...


1

I think you are confusing a few different things here. 1: database normalisation. The question answer you link to is about normalizing out repeated data into a second table with a FK. The key needent be an int and will be repeated, so if your data is JUST the key you are already normalized. 2: ints as keys (due to space?) You suggest that using an int ...


1

Enums are not only faster to insert, delete, update and read. They also take up less disk space, and most importantly: they ensure that you can only store a limited set of values. If you create a field, and you only want to allow values "male" or "female", then an enum is handy to make sure that nobody stores "man". Text fields are just a bad solution to ...


1

It is not unusual to have several keys in a table and there's nothing fundamentally wrong with that. If a table has more than one key then the choice of "primary" key is unimportant - or only as significant as you want to make it. Your boss is correct that it is usually a good idea for every SQL Server table to be clustered. Guids don't make for good ...


3

Actually, internally the database is not going to store your string representation of your enum in every record. It is effectively making a lookup table of it's own and translating the stored integer value to string (and back) when you interact with the database... From: http://dev.mysql.com/doc/refman/5.0/en/enum.html If you retrieve an ENUM value in a ...


3

The problem is not space. Because space is cheap. It really takes a good book to explain what is wrong with repeating texts in a column, but it's a bad idea. You will get it when you read about database normalization. Database normalization is what strong typing is for programming languages. You can live without, especially for very small projects, but ...


2

Advantage: GUID is unique so in the long run if you run into this scenario where you can join using just Guid rather than PK+other field. ex:there sales, order, adjustment, and there is stock, instead of join using PK and transaction type, you can just join using Guid since it 99.9...% guaranteed unique. By using GUID you can generate GUID from code so it ...


1

Well as already stated in the comments of the other question, this is nonsense if you won't query using the id at all. If you use just the GUIDs then leave out the clustered index and just setup the no clustered index on the GUIDs. But never use the GUIDs as clustered index unless you want to have a stress test on your I/O subsystem. ;-)


2

You are being squeezed in both directions, with tricky input and a tricky back-end. In an ideal world, you'd challenge some of these constraints, but I get the impression you won't get anywhere. The algorithm you describe sounds like your only option, although I think it can be improved on a little. Call listTransactions and store all transactions in a ...


2

This sounds like a case of premature optimization to me. Your project is very, very tiny compared to a lot of applications running on a normalized database system. I know ambitions can get the best of you, but worry about global scaling if and when that becomes an issue. Until then, I'd stick with normalized tables (3rd normal form is my go-to). Any RDBMS ...


3

I cannot be sure that each CSV file has only been processed once... You might want to attempt to solve your question by handling this first. If I am getting this right, the crux of your problem doesn't appear to be individual duplicate transactions (since you mentioned "I know for sure that there are no duplicate records in each CSV file"), but to ...


2

Considering you stated that it is about REST API, think that each call to the API will result in some network traffic, where latency and transfer time apply. So I would say the less calls to the API you make (store results in memory to search through), the better. However, when you work with the API, you don't always have a choice: if the API itself is not ...


0

Introducing an extra service tier always have a cost in complexity and performance overhead. There are some specific kinds of architecture where introducing a shared service tier (like a REST API) may improve perforce due to shared caching - but it sounds like it is not the kind of architecture you have. Consider an architecture where you have multiple web ...


1

You are right, there is no clear benefit to introduce a REST API layer between a web app and a database, and it has a cost in complexity and performance overhead. The reason you are getting contradictory answers is confusion about what is the 'client' in your architecture. In your architecture (if I understand it correct), you have browsers interacting ...


2

You're in the right neighborhood. Most PHP applications will have active sessions for all page views, regardless of whether a user is logged in. It would essentially do a session start() at the beginning of the script, and a save() at the end. That session would exist for the entire visit to the site, or longer, depending on how you have PHP set to expire ...


0

It depends. Obviously, the more layers in your code the slower it goes. But... there comes a point where direct end-to-end performance doesn't matter as much as scalability. If you have 1 user accessing your database on a local PC, it can go fast. If you have a thousand users accessing the same DB on the same PC, chances are you're going to see them all ...


0

If find it hard to answer this question. The correct general answer should be: it depends. The way I see it with REST: 1. You make an object in your code to call the REST method 2. Call http method 3. Code inside your REST API queries the database 4. Database returns some data 5. REST API code packs up the data into Json and sends it t your client 6. ...


3

If you're concern is speed, then yes a Rest service will be slower for the reasons stated above. However, speed of the type you describe is rarely the primary concern and if it is, can be addressed in other ways. Premature optimisation is the root of all evil. Consider if your primary concern is interoperability (mobile, web, B2B), now REST is very ...


8

When you add complexity the code will run slower. Introducing a REST service if it's not required will slow the execution down as the system is doing more. Abstracting the database is good practice. If you're worried about speed you could look into caching the data in memory so that the database doesn't need to be touched to handle the request. Before ...


0

I don't know where you get lost, but it is pretty clear, when you're using REST API you are doing extra step, and extra step "always" mean slower when programming involved. There's pros and cons, but if you can access database directly from your application it always better to call it directly instead of using Web API, of course if you use Web API you can ...


2

I think the other answers provide a wide spectrum of reasons as to why operating systems do not rely on relational databases internally/exclusively so I'll just share an interesting piece of information that I once stumbled upon. Apparently, there are technologies that allow you to mount relational databases as file systems when their use is justified. ...


0

Databases has special index type for such kind of searches based on Minimum Bounding Boxes, called r-tree. Let's assume that we have 1M places defined by x/y in our database and we want to find all points in radius from our position we first need to build MBR containing search circle (center point in this same place and edges 2r), search data and in next ...


3

I was hoping there would be some kind of database optimization Databases with spatial / geospatial extensions allow to store spatial objects and fast query operations like "is point in certain area", supported by so-called spatial indexes. The exact set of features as well as the syntax differs from DBMS to DBMS, but I do not know of a database which ...


0

You want to find things in a two-dimensional space easily. That is similar to the more common problem of finding things in a one-dimensional space; the solution there is to sort your data and then find things in it in O(log n) time via binary search. You can't do exactly the same thing for two-dimensional data because the ordering is not the same for the ...


2

The main function of any OS is to facilitate interactions between applications, hardware and the users. So.. why don't Windows/Linux OS use relational Databases (RDBMS)? This is a question of biblical proportions, but the short answer is: There is not any real benefit to be gained from using a complex structure such as an rdbms as a file system. ...


0

also, if you want to store ALL changes to the DB over time, you might want to check out logging (http://stackoverflow.com/questions/3394132/where-can-i-find-the-mysql-transaction-log)


3

It is no problem to have data "not consistent across all rows" in a relational database. This is precisely what relational databases are designed for! You create a table for the data which is common across all activities, and then you create separate tables for data specific for kind of activities. E.g. a football table with a score column, a cycling table ...


3

Take a look at PostgreSQL. You can store JSON in it in a type-safe way. It supports indexing so you can combine relational data with JSON documents pretty well. It is also fast, there are plenty of benchmarks. Also, if you never used NoSQL before, please read this: Why You Should Never Use MongoDB (don't mind the title, article is pretty constructive) As ...



Top 50 recent answers are included