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 ...
MDCCL's user avatar
  • 8,520
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 ...
J.D.'s user avatar
  • 37.5k
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 ...
Scott Hodgin - Retired's user avatar
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 [...
Tom V's user avatar
  • 15.7k
6 votes

Efficient Database schema for multi-lingual content?

The least work would be to put a language code in each table. The problem then is there is no way to show that, say, post 612 (English) is a translation of post 943 (Spanish). To achieve that level ...
Michael Green's user avatar
5 votes

Relational tables with same content, custom solution

I would keep them separate. Although the details differ, your circumstance smells a lot like the One True Lookup Table antipattern. By combining you will have problems defining foreign keys (which ...
Michael Green's user avatar
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 ...
Evan Carroll's user avatar
  • 63.1k
4 votes

Efficient Database schema for multi-lingual content?

I have used the following style of schema for multi-lingual support: +---------------+ | CATEGORIES_B | +---------------+ | category_id | | category_code | | ... ...
DrabJay's user avatar
  • 339
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 ...
Joel Brown's user avatar
  • 12.5k
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 "...
Hannah Vernon's user avatar
  • 70k
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 ...
Joel Brown's user avatar
  • 12.5k
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 ...
Solomon Rutzky's user avatar
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 ...
Daniel Hutmacher's user avatar
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'...
Hannah Vernon's user avatar
  • 70k
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 ...
Aaron's user avatar
  • 4,075
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'...
J.D.'s user avatar
  • 37.5k
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 ...
Erik Darling's user avatar
  • 40.8k
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, ...
db2's user avatar
  • 9,658
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 ...
Michael Green's user avatar
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 ...
Glorfindel's user avatar
  • 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 ...
David Browne - Microsoft's user avatar
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 ...
TommCatt's user avatar
  • 2,139
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 ...
Rick James's user avatar
  • 78.1k
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/...
Muab Nhoj's user avatar
  • 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 ...
Rohit Gupta's user avatar
  • 1,640
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 ...
GingerBreadMan's user avatar
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). ...
jmk's user avatar
  • 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 ...
Rick James's user avatar
  • 78.1k
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 ...
Salvatore Shiggerino's user avatar
1 vote

MSAccess: creating a new row in a related table and linking it back to the "master" table

Your question is legitimate and there are some reasonable patterns that you can follow to get it working. However, I also feel like your question demands some comments regarding Access forms and ...
C Perkins's user avatar
  • 477

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