Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [design-pattern]

79 questions
27
votes
1 answer

Composite Primary Key in multi-tenant SQL Server database

I'm building a multi-tenant app (single database, single schema) using ASP Web API, Entity Framework, and SQL Server/Azure database. This app will be used by 1000-5000 customers. All the tables will have TenantId (Guid / UNIQUEIDENTIFIER) field.…
Reynaldi
  • 371
  • 3
  • 5
12
votes
3 answers

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People --------< Likes The logic is: 1 people can have many…
Ragu
  • 221
  • 1
  • 2
  • 4
8
votes
2 answers

Do agile software development approaches apply to SQL?

I would like to learn if agile software development methods/principles/patterns are valid to SQL programming as well. If yes, where would be a good place to start learning about that? Are there any articles or books targeting agile development in…
Κύριε ἐλέησον
  • 165
  • 1
  • 5
7
votes
1 answer

Test Driven Design for postgres procedures

I am looking to introduce a test driven design style to writing my stored procedures for a postgresql database implementation. I have seen that pgTap is a popular unit testing tool for postgres and correctly allows for the tests to be written in SQL…
Russell Ormes
  • 193
  • 1
  • 9
6
votes
2 answers

Relational Design - One table, two foreign keys or Two tables, one foreign key each

Looking for some advice related to optimal design in the following scenario. There is a Cases table (represents cases of inventory) There is a LocationInventory table (represents locations with inventory) Then I have an InventoryNeed table or…
crichavin
  • 525
  • 1
  • 6
  • 19
5
votes
2 answers

Is there a well-known name for this "poor man's ref. integrity" schema design pattern?

Is there a name for the following database schema design/pattern? My eventual goal is to find more literature about the subject. Today's cursory net search was too full of generic words to be able pin down the term (if any exists) for this kind of…
starlocke
  • 191
  • 6
5
votes
2 answers

Data Modeling for different roles

This is my first post here, so I hope its clear enough. I want to make an application for people who sell, buy, rent and fix a certain product. I need to create a database that would keep the data of the product and of the accounts (with logins and…
raygo
  • 151
  • 3
5
votes
4 answers

best ETL design to transfer transaction tables records into the data-warehouse

I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table. but for transaction tables, that have many records, i…
AmmarR
  • 2,826
  • 3
  • 28
  • 33
5
votes
1 answer

What's the difference between these three versions of TSQL snippet?

Version 1 DECLARE @key INTEGER = 33, @val INTEGER = 44; BEGIN TRANSACTION; INSERT dbo.t([key], val) SELECT @key, @val WHERE NOT EXISTS ( SELECT 1 FROM dbo.t WITH (UPDLOCK, SERIALIZABLE) WHERE [key] = @key ); IF @@ROWCOUNT =…
Fajela Tajkiya
  • 1,239
  • 9
  • 23
4
votes
1 answer

Optimum design / structure for product data database?

What would be the optimum design/structure for product data database, where each product have many variations and many attributes? Attribute sets vary between products, and some attributes have values common for all variations of a given product. A…
ellockie
  • 143
  • 5
4
votes
1 answer

Most efficient ordering post database design

I have posts table that is has post_order column. I store order of each post in it. when I change the order of a row from 25 to 15, I should update all the row from 15 to end. It's good for few rows, But in thousands rows it's worst. Is there any…
Arash Mousavi
  • 673
  • 2
  • 12
  • 21
4
votes
2 answers

Geographic Hierarchies in a OLAP Cube / Data Warehouse

Are the following geographic hierarchies correct? Do any US/Canadian area codes cross state/province lines? Do any US/Canadian counties cross state/province lines? Are there countries with counties but no principal country divisions…
Neil McGuigan
  • 8,653
  • 5
  • 42
  • 57
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

Numerical data with lots of NULLs: 6NF or document database?

I have a source of numerical data mostly comprised of sensor readings, physical quantities and flags. Each associated with a datetime. | id | timetag | sensor1 | sensor2 | flag1 | ... …
IGRSR
  • 141
  • 3
4
votes
3 answers

If the query for database too complicated, does it means the design of Database is bad?

I am new to database, and below is one of my query with it's explanation, I feel it's too complex. It works, but I am worried the design is bad. Provide a list of customer information for customers who purchased anything written by the most…
bagMan
  • 163
  • 6
1
2 3 4 5 6