Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [application-design]

103 questions
50
votes
4 answers

Are individual queries faster than joins?

Conceptual question: Are individual queries faster than joins, or: Should I try to squeeze every info I want on the client side into one SELECT statement or just use as many as seems convenient? TL;DR: If my joined query takes longer than running…
Martin
  • 2,420
  • 4
  • 26
  • 35
33
votes
4 answers

What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?

I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better: delete rows and insert new ones, or update already existing rows For me it is easier to just delete all the rows and insert…
adopilot
  • 2,443
  • 8
  • 31
  • 46
6
votes
1 answer

Entity-relationship diagrams and application program functionalities

It seems that the core functionalities of an application program I'm working on are nothing but associative entities. Hence one-to-many relations sort of produce "metadata" that will only feed (one way or the other) the associative entities for our…
Jason Krs
  • 335
  • 1
  • 5
  • 17
5
votes
7 answers

Policies RE database triggers in well-designed applications?

I've heard all kinds of horror stories around gremlins living in database triggers, and--worse--systems being brought down by the addition of a trigger that caused a chain of cascading ones. I'm considering implementing a strict policy about the use…
Bryan Agee
  • 801
  • 1
  • 8
  • 16
5
votes
2 answers

Efficient Database schema for multi-lingual content?

Assume I have a table for blog posts, posts, and schema as id author title content and each post can belonged to some categories id name descrtiption So a blog post can belong to multiple categories. Obviously title and content in posts, and name…
Yoga
  • 549
  • 3
  • 8
  • 15
4
votes
2 answers

Database instance and database

I did some research and finally came to this conclusion: Database instance = processes + memory Database = (physical) log files + control files + data files. Is this understanding correct? Can an application use multiple database instances of the…
AKL
  • 43
  • 4
4
votes
3 answers

For what type of data it's better to use relational, and for what type of data, non-relational databases?

I am trying to write my first big backend project. This is a mobile/web application like Instagram but for different purposes. As I searched through the internet I found that Instagram uses PostgreSQL and Cassandra as it's main databases. But I…
user20551429
  • 69
  • 2
4
votes
1 answer

barcode inventory design - transaction or master?

I am building a inventory software for mobile phone retailers. There are several things confuse me. Ok, we have: Product Product Attributes Barcode Stock In Batch (Purchases/Return, etc) Stock Out Batch (Samples/Sales, etc) Stock Transfer Batch…
cww
  • 369
  • 1
  • 12
4
votes
2 answers

How to prevent duplicates while storing result from different application

For my case multiple application uses data from db based on date, this data is date specific so if for a date data exist application should just read it otherwise it should fetch information from different site and do some calculations to create…
techExplorer
  • 261
  • 1
  • 4
4
votes
2 answers

Database normalization: Describing trait - foreign key to table, or varchar field with value?

My colleagues and I are having a discussion about the normalization of descriptive traits in the database, such as "status" or "type". Let's call the central table of the discussion "Order". In my regular design-approach, I would define another…
Sebastian
  • 143
  • 1
  • 3
3
votes
2 answers

What is the industry term for superset of "metrics" and "dimensions"?

If I want to refer to measures/metrics and dimensions with a single term, what would be a good industry-accepted expression? Metrics is used commonly in UIs as well such as Google Analytics or Tableau. The context for my question is for a UI. I'd…
Will E
  • 39
  • 3
3
votes
2 answers

Can I use a single stored procedure to operate on different schemas based on the executing user

I have thousands of schemas with same set of tables, Each user has a default schema. But I dont want to create copies of a single stored procedure in every schema. Can a single stored procedure access the tables of user specific schema. I have…
mayur rathi
  • 183
  • 1
  • 6
3
votes
3 answers

Relational tables with same content, custom solution

So let`s say that I am building an app where I can add comments for FOO. Normally I would have something like this: Table foo and table foo_comments, where foo_comments contains id, foo_id and other columns. Right now this is the only way that I am…
Alex
  • 131
  • 3
3
votes
1 answer

Separate Schemas by Industry or by Application functionality?

I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web based. I might use Postgresql, I would go for a Single…
tmbsundar
  • 323
  • 1
  • 7
2
votes
2 answers

Overhead of decomposing complex query into multiple simple queries?

What kind of overhead should I expect when replacing a single complex query with multiple simple queries? My goal is to improve the readability and portability of all SQL code so I will be favoring simple constructs and replacing database-specific…
Gili
  • 1,049
  • 1
  • 16
  • 31
1
2 3 4 5 6 7