9
votes
Accepted
Entity-relationship diagrams and application program functionalities
You touch several subjects, therefore it is important to establish their scopes and the links that exist among them.
Firstly, if the specific business domain you are working with implies (a) multiple ...
- 8,540
7
votes
For what type of data it's better to use relational, and for what type of data, non-relational databases?
may I know how to decide for what services or what type of data/application it's better to use SQL or NoSQL databases?
Use a SQL database if any combination of the following is true:
You have ...
- 38.7k
6
votes
What is the industry term for superset of "metrics" and "dimensions"?
Dimensions and Measures are the two main components of cubes. The dimensions are basically ways to look at the data while the measures are the data.
When writing MDX the measures are a member of the [...
- 15.7k
6
votes
SqlDataAdapter vs SqlDataReader for DBAs
In my experience, I think of SqlDataAdapter sort of like a .net SELECT * INTO #TEMP except the #TEMP is on the client computer. The entire result set is read from SQL Server and that connection is no ...
- 23.9k
4
votes
How to use a foreign key?
However, I'm not sure what would be the best practice to use identifier as a foreign key. Should I use as reference the value of the foreign key like this
A couple of points before hand.
There is no ...
- 64.2k
4
votes
Accepted
DB design to avoid versioning inconsistencies between the application layer and the database layer
The approach you're think of is called Entity-Attribute=Value (EAV). If you Google around for it you'll find very quickly that it is almost universally reviled. In short, it's generally a bad idea ...
- 12.6k
4
votes
Omiting schema names to manage access to SQL Server databases more flexibly
Short answer here is No, it is neither "safe", nor "expedient", and arguably not more flexible to omit the schema name. In fact, doing so is one of the classic ways of "...
- 70.3k
3
votes
How to handle removing ASP.NET users where their UserID is tied to rows that should be maintained even after a user is deleted?
What I recommend is separating out the concern of "Who are people we've heard of?" from the concern of "Who can get into the system now and how do they do that?"
What this means in practice is that ...
- 12.6k
3
votes
Accepted
Using Text Fields As Booleans
If you have a system with a large number of tags it's probably more efficient management-wise to use a relational system between products and product-tags.
i.e.:
IF OBJECT_ID(N'dbo.ProductsTags', N'...
- 70.3k
3
votes
Can I use a single stored procedure to operate on different schemas based on the executing user
I do not believe that what you are trying to accomplish is possible, at least not without an extra, over-complicated layer of Dynamic SQL.
Since you have already gone through the trouble of creating ...
- 69.4k
3
votes
Accepted
Can I use a single stored procedure to operate on different schemas based on the executing user
You could probably use dynamic SQL,
CREATE PROCEDURE [StoredProcedures].[Insert_Table1]
AS
DECLARE @sql nvarchar(max)=N'
INSERT INTO table1 VALUES (NEWID());
INSERT INTO table1 SELECT col1 FROM ...
- 9,133
3
votes
For what type of data it's better to use relational, and for what type of data, non-relational databases?
Full disclosure: I am an employee of DataStax, and a Cassandra MVP (so I'll do my best to hide any bias).
They've presented on this publicly a few times (see below), but at a high level, Instagram ...
- 4,205
3
votes
Database design for tracking student exam performance at topic level with possibly 10 billion rows
If each user attempt each question only once, we will have 100K * 100K rows for attempts
I think it's unlikely every student would answer 100,000 questions, let alone even one student to do so. That'...
- 38.7k
3
votes
Accepted
What do developers need to know about Read Committed Snapshot Isolation?
Stop using NOLOCK hints
Stop setting transaction isolation levels to READ UNCOMMITTED
Add appropriate READCOMMITTEDLOCK hints to queries that require non-versioned data
Mind transaction duration, as ...
2
votes
Accepted
Database Designer's Block
My knee-jerk reaction would be to add a CourseSchedule table between Course and PersonTranscript. The same course could obviously be offered at different times and places, with different instructors, ...
- 9,688
2
votes
Accepted
Best way to design a database for a bakery store?
You have an ingredient; let's call this "strawberry mix, packet, 100g". You also have a product; let's call this "strawberry mix". There is a Product_Ingredients row which maps the product "strawberry ...
- 25k
2
votes
Insert/update data into relational database tables only after administrator approval - how to approach it?
You basically have two options:
Extend each table with a 'Status' column, with possible values 'New', 'Approved' and 'Rejected'. Maybe another column for remarks by the administrator, and one to ...
- 2,201
2
votes
SqlDataAdapter vs SqlDataReader for DBAs
There is usually not a significant difference between the two, from a SQL Server point-of-view.
The worry is that the DataReader the queries will run longer, since the client will perform some work ...
- 47.4k
2
votes
Accepted
How to use a foreign key?
Your table seems to have a perfectly good key candidate (identifier) yet you also create a surrogate key. Why? (I do not hold to the rule that all tables must have a surrogate key.)
However, once you ...
- 2,149
2
votes
Accepted
How to UPDATE data without locking every record the search encounters?
UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3 = 3;
Needs this composite index:
INDEX(col1, col2, col3) -- in any order
So, if you start it with col1, col2 (in either order), that ...
- 78.9k
2
votes
For what type of data it's better to use relational, and for what type of data, non-relational databases?
MySQL and PostGres are both really versatile; MySQL especially comes pretty well configured out of box. MySQL also has a really long list of notable companies that use it : https://www.mysql.com/...
- 411
2
votes
Accepted
Changing data type of an existing column of SQL database
You are increasing the precision (number of digits). But for sql-server the same 5 bytes are used for precision 1 to 9. So the field length will not change. Which means that even the database size ...
- 1,750
1
vote
When should I reference the User record or User Profile record
You have an option to create two tables with user and profile data or together as one table with both the data. This depends upon some factors.
Two table scenario:
In case of large set of fields and ...
- 483
1
vote
When should I reference the User record or User Profile record
If both tables - user and user_profile - have as primary key the column user_id, and there is a one-to-one mapping between rows in these tables, what you have there is a vertical partitioning of a ...
- 25k
1
vote
How to scale with MySQL (when not ready to scale properly)
PARTITIONing does not save space. Each partition has 4M-7M of "free" space. That can add up. Partitioning can be used for removing data from the server (or moving to another server) "old" data. ...
- 78.9k
1
vote
Should you update child states based on the parent?
It's opinion based, but I would err on the side of updating the bare minimum when a status changes.
If you cancel an event and only update the Event row indicating cancelled then you minimize the ...
- 23.9k
1
vote
Any way to represent simple AND/OR business logic in a database?
If you had billions of students and the rules were fixed, I'd recommend a stored procedure which performs the logic on rolling subsets, saving the results in another column or table. Since you likely ...
- 111
1
vote
Common practices to determine what database technology to use
Adding a no-answer as I don't know of a formal method for choosing a database architecture (remember you buy a architecture with special backup requirement, HA feature, hardware requirements).
...
- 444
1
vote
Accepted
Insert/update data into relational database tables only after administrator approval - how to approach it?
I'd build a UI specifically for the admin and his approval. It would display the things that are awaiting approval. He would click [Approve] or [Reject] (etc?) by each one. That would go off to the ...
- 78.9k
1
vote
Accepted
What sort of database setup is used for an App Store or Google Play Store?
If it's just for a demonstration and won't be deployed to any users, and embedded database, for example SQLite, is a good choice. Not that there's anything wrong with running a local or shared ...
Only top scored, non community-wiki answers of a minimum length are eligible