Hot answers tagged

35

I work with an application that uses bitmasks to store user role assignments. It's a pain in the butt. If this makes me biased, guilty as charged. If you're already using a relational database, it is an anti-pattern that violates most relational theory and all the normalization rules. When you build your own data storage, it may not be such a bad idea. ...


23

You have already named the relevant pros and cons: Bit fields save space. They store data in the record itself, so you don't need JOINs to find them. (But individual flag fields in the record would do the same.) They are badly readable if you want to work productively with raw SQL output. Deciding what to do requires more info: Just how scarce is disk ...


18

A few years ago I worked on an application that was written by somebody who had clearly never learned how SQL databases work. I was given a problem report to fix -- the main status summary page, which had always been slow, had now started to be so slow that it was hitting the server script execution timeout (of 3 minutes) during rendering. It seemed that ...


14

If you're really, really, really strapped for disk space, then you might consider bitmaps for user permissions. If performance is your worry, then forget about them altogether, because picking them apart will actually be slower. You can't index a bitmapped field meaningfully, resulting in database table scans, which are [almost] always a performance killer....


7

Back when storage was expensive, the boon with bit masks was that they saved space. In the days of big data, this isn't the issue it once was. Taking the example you cite - having roles stored as a bit mask would be something of a code smell from a database design point of view as it would violate first normal form. In this sense, they're an anti-pattern. ...


5

.net already uses connection pooling, so when you create/dispose a connection, you are not actually opening and closing a database connection, just fetching and returning a connection to the pool. So you don't really get any benefit by changing to Open/Close, and the using construct guarantees that the connection is returned to the pool correctly even in ...


5

making queries on things like temperature to sales of specific categories of products This (Queries) is one area relational databases are designed for. You are better off with a database with SQL support.


4

Put the data in a data base. Write a function which pulls all the data from the database and populates the arrays. Pass the populated arrays to the unchanged calc function Write a function to write a database using the resultant arrays from the calc function.


4

Don't discount the possibility that you'll need to actually go into the database and query it directly as part of a debugging process. If you ever end up doing that, you'll definitely want to know all about the database technology and how your particular database is structured. Maybe it won't happen. But if it does (and in my experience it always does at ...


4

We have a set of SQL scripts that we use to create and modify the DB between versions - as its text, its stored in the VCS and tagged along with all the other code. One of the scripts is a "set up default data" one, and another is a dev-specific script that sets all the usual environment, such as developer users. When you want a new DB, its trivial to run ...


3

Only up to a point As a software developer, you'll probably have to query and update the database, and knowing how the DB operates is critical to avoiding bad queries, inefficient joins and so on. You might have a dedicated DBA who can decide where to add indexes ir partition the database, but you can't count on it, not in small companies and not always in ...


3

would it be a 'bad idea', to implement a policy/practice to always persist data and to always implement 'auto-updating' (if possible) created, updated and deleted properties? Persistence and auditability (not stated but implied) are valuable goals, and it is good to think in that direction for cases where you need them. That said, to answer your questions: ...


3

Let's take the example of a major ERP on the market: all the master data and most of the transaction data carry 4 fields: the author and the time stamp of the record creation and of the last record modification. all the critical field changes (it's customizable what is critical) are logged with the author of the change, the timestamp of the change, the ...


3

No, it is not too verbose. I'd say it's close to being a best practice. Having created and modified dates on a per record basis allows one to see how stale that particular data row is. Most systems I have encountered have data retention requirements. By having these dates, one can have a data purge process to keep only a certain amount of data in a ...


3

No. Unless you are on a really crappy O/S (or, to be precise, your ODBC library and/or database access library, which may or may not be part of the O/S proper), your code is not actually creating/using/closing database connections. It is creating/using/closing database connection objects, which are a wrapper for the underlying connections. The underlying ...


2

You could create a table that stores courses that can be taken with each other: CourseCombination (id, course1id, course2id). Then you could create a table that stores a single value representing the time frame a student can take any given combination of courses: CourseCombinationTimeFrame. Then in your application create a place for the system ...


2

Main thing is once the product is delivered, and if there is any need of changing the business logic then they want to be able to that themselves That's potentially a huge issue. You need to be able to scope this, and determine what they're likely to want to change e.g. switch between a couple of behaviours? Then provide a config/flag to provide that ...


2

I think you're overthinking things. You can use a traditional SQL-based RDBMS. It may or may not be fast enough (although I suspect you're worrying prematurely about optimization), but the only way to tell would be to try it. Just make sure that you write your code that interacts with the storage system in an abstract-enough way that it is simple to ...


2

Most databases have tables that store schema information about tables that are being maintained. For example, in SQL Server: SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @Table ORDER BY COLUMN_NAME Will get you a list of columns for a particular table. However, in your case, I would just ...


2

Server should expose those REST services, which fullfil your need in front-end for the single page application. When an application is developed which acts as the client for the data available through REST services, there is quite often communication between the back-end and front-end team. Because of this and the demands from the front-end team, the REST ...


2

The database can only hold 1 write concurrently and does not support transactions. If this value is exceeded, the database responds immediately with an error code XXXXX. This is extremely bizarre for any modern database, but let's accept the premise. At a high-level, there are 2 approaches to concurrent data modifications: Pessimistic - This is lock-...


2

Reading the specs, the main point of your requirement is: An user should receive an immediate response that vote was accepted. To achieve that, you could separate out the vote-taking-part. Each of your two instances need to talk to a component responsible for that. Then the problem arises, this component becoming the bottleneck in your architecture. ...


2

If you profess to knowing nothing about cars, would I be happy with you servicing the brakes on mine? I think not. Databases are noticeably different from the data structures that you're used to working with in programming. They have their own oddities and idiosyncrasies and other things that will bite you in the Application Performance if you don't have ...


2

It is absolutely worth the time! Being a full stack developer enables you to efficiently produce value-added solutions. I've seen all too often communication breakdowns and silo'd development... Triple the development time and half the quality. At the end of the day, the more skills you have, the more valuable you will be.


2

The only advantage to using bitmasks is if the bit fields' meaning is not static. Relational tables only work well if you know ahead of time what each field is on a record: you have to identify the fields in the CREATE TABLE DDL statement after all. If the meaning of each bit field is configurable at runtime, or otherwise not known ahead of time, then it ...


2

Lots of people use version control with databases. There's more than one pattern for doing so and any number of tools but fundamentally its a sound way to go. Similarly I strongly advocate for local databases for developers if at all reasonable - you will get merge issues if you're changing the schema a lot, but you should be able to work out practices to ...


1

There is no point introducing a cluster of app servers fronted by a load balancer, if all are pointed at a common "database" which is not designed to handle concurrency. I wouldn't call it a database at all because that implies it handles concurrency as one expects from a modern DBMS. Since the requirement is you cannot change that piece of the ...


1

The guiding principle at work here is probably "if you ever need to run a query on the data, and not just display it in its native form, then the data needs to be first-class rows and columns, not some JSON stuffed into a single field."


1

I think most applications should have them. These values are more of a convenience for trouble-shooting and other support needs. Soft deletes have benefits as well, but you always have to include them in your query logic. Just because you can use them, they're not enough. Many auditing needs require more data and sophistication even beyond logging. ...


1

The general term for having off-line "child" databases is replication. I don't believe there is standard term for those child databases only containing a defined part of the central database. Call it partial replication, if you like.



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