Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

0
votes
1answer
15 views

Adding Multiple Individual Indexes vs One Combined Index for Multiple Foreign Keys

Background I have a "join" table that is used to join one "transactional" table with a "category" table based on multiple columns (and through several joins): TxnTable(Entity1Name, Entity2Name, ...
-1
votes
1answer
23 views

How to create a Hash Computed Column?

Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for ...
2
votes
0answers
37 views

What Is the Datatype for a Persisted Computed Column? Should I Convert/Cast When Creating It?

What is the Datatype for a Persisted Computed Column? (a) Does SQL Server automatically infer it? (b) Also is better to cast it as a datatype, so SQL does not spend time to find what data type it ...
2
votes
1answer
26 views

Suitable NoSQL DB design for a fleet of IoT devices

This is my use case: 10,000 IoT devices Each IoT device sends its environmental data (temperature, pressure, etc.) every 30 seconds Data of each device will be kept for 24 hours The main requirement ...
0
votes
0answers
28 views

How to design entities for matrix-based user-data

I am developing an application where exists an application user. For the current moment, application user must be either 'company representative' or 'individual entrepreneur' by juridical status. In ...
0
votes
0answers
23 views

data model for C 2 C business

I would like to find out what table relation would be idea for this case a Customer to Customer business transaction where one client provides a service to another for a charge. So this is purely a ...
1
vote
0answers
22 views

SQL Server Data Warehouse stored procedure location

We are building a Kimball data warehouse. We have a Staging Database, and Final Datawarehouse. We have a stored procedure which creates the table CustomerTransaction. Where is the best location to ...
0
votes
1answer
28 views

Recursive Relationship

I have an application, which contains two Models Question and Option Each Question has many Options Each Option may have many Questions (and this Question may have many sub Options) so on and so ...
0
votes
1answer
51 views

SQL Server Stored Procedure Slowly Changing Dimension

Can someone code review this slowly changing dimension for our Kimball Data warehouse? This is Type 2 dimension. I reviewed a lot of code on the internet, however I want to use non-TSQL specific ...
-3
votes
1answer
22 views

I need help with normalising the data derived from a survey

Background information: I have been assigned to make a survey on E-sports and build a database. My survey can be accessible here https://devpatel6.typeform.com/to/MB1DYP . In brief, this survey ...
0
votes
0answers
22 views

Database design - linking and normalizing tables issue

I am seeking help in designing an Entity Relationship database design for my contacts database and resolving the following issue. TblInstitutions TblDepartments TblSections TblEmployeeContactInfo ...
1
vote
0answers
47 views

Optimize Data Warehouse Design

I am working on a project for school (lab, not homework) and we are asked to fix any design problems that we found in the data warehouse implementation. It seems like we could probably get rid of a ...
0
votes
1answer
22 views

What is the foreign key between two children in the same hierarchy?

I'm trying to map an entity relationship design into a relational database. Under STAFF entity, I have 2 children are BUSINESS MANAGER and EMPLOYEE. My teacher requests to add a 1-M relationship ...
2
votes
0answers
33 views

Double Entry Accounting Schema

Setting up a double entry accounting system for personal use and to help manage a really small business. Trying to put a few features that seem relevant now. Business Rules The logic, for those not ...
1
vote
2answers
57 views

Are updates the only disadvantage of denormalization?

I am trying to understand what are the disadvantages of denormalization. Say that I have the following database (School_has_Student is a denormalized table): I have read that the problems that you ...
0
votes
1answer
68 views

database and table design for billions of rows of data

basically i got 2 tables: header, details tables. CREATE TABLE `header` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `RECORD_DATE` datetime DEFAULT NULL, `TICKER_ID` int(11) DEFAULT NULL, `...
1
vote
0answers
20 views

Multi tenant .NET Core application

We are designing a new multi tenant .NET Core 2.1 application progra, where we are using one database per tenant. We can give each tenant a different subdomain so that we can base the tenant lookup ...
0
votes
1answer
61 views
+50

Design with inheritance and two 1 to many relationships to the same table

Quick schema overview: Below is a schema being a subject of my question. In the design there are two "regular" tables questions and notes as well as two "abstract" tables: elements, with "concrete" ...
1
vote
0answers
31 views

Primary key: (a) set it on a single column for two distinct data points or (b) set it on a combination of two columns, each for a separate data point?

I am designing a database for an e-commerce company. Each product will be identified by a unique ID (SKU). We want to format the SKU as LLLL000. LLLL is the parent product, for example Iphone X. 000 ...
0
votes
0answers
43 views

Should Clustered Columnstore Index be the default standard now for Data warehouse?

Should Clustered Columnstore indexes be the default standard now for db partitions with 1 Million rows > ? We have a Kimball data warehouse, with Fact and Dim tables. Our Kimball data warehouse is ...
0
votes
0answers
31 views

How do you model a recurring order in a shop?

I am making a B2B-shop system for a customer where their customers can order items from their shop. The system needs to handle placement of recurring orders like: "Every tuesday of the week send me 4 ...
0
votes
2answers
33 views

Difference between OLTP and Inmon Data Warehouse

What is the general difference between OLTP and Inmon-style Datawarehouse? It seems like they are the same. OLTP just copies all the tables into a Read-Only Datawarehouse DB. I know what a Kimball ...
0
votes
1answer
29 views

Why should I save both markdown and the converted HTML in my database?

First of all, forgive me if this is the wrong place to ask this or if this has been answered somewhere and I just can't seem to find it. So you have a scenario where you have an article/news web app ...
-1
votes
0answers
43 views

Should have primary key when rows are read in groups, and deleted individually, in a huge table [closed]

I have a table whose candidate key comprises 5 columns: 3 of bigint/integer type and two of varchar(255) type. The table will typically have a huge number of rows (millions). The rows are read in ...
1
vote
1answer
44 views

Data Warehouse Kimball Customer and Customer Address Dimensions

I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc. In Kimball data warehouse, would I utilize a bridge table to go between ...
-1
votes
0answers
11 views

How to structure wide-format timeseries data

I deal with atmospheric data that is currently wide-format, like this: |timestamp | temp@2m_mean | temp@2m_stdev | temp@10m_mean | temp@10m_stdev| |1/1/2018 00:00 | 15.23 | 0.12 ...
0
votes
1answer
20 views

Address Parts - Hierarchy vs Flat

I'm using Google to geocode addresses, which breaks down an address into geographic units like country, state, city, etc. Depending on the address being geocoded, some geographic units may or may not ...
0
votes
1answer
40 views

one to many design struggle [closed]

I have two tables : CREATE TABLE fiber ( fib_id uuid primary key default gen_random_uuid(), fib_number not null, fib_object uuid not null reference cable, --useless here but imagine a cable ...
-1
votes
1answer
25 views

Determining what user data fields to protect within a database?

Besides passwords, credit/debit card info, social security info, passport info, what other types of data fields should be hashed to give it an additional layer of protection within any database? ...
-1
votes
0answers
59 views

Storing large amounts of disjointed timeseries data in Postgres

Background I need to collect and process batch log files containing sensor data. Each contains its own relative time array (starting at some arbitrary number and increasing in intervals of a few ms) ...
-1
votes
1answer
34 views

Inserting data into a 3NF database [closed]

I have 6 tables with ~2-15 attributes each, each one of these 6 tables has an ID which is constrained from another table. This 7th table has its own ID and the 6 other table ID's, as well as a date ...
1
vote
0answers
16 views

When to use role codes and when to use supertype / subtype

What's the best practice to follow when designing a data model when it comes to choosing between using supertype/subtype and role codes? To be clear, when i say role codes I mean a table design like ...
-1
votes
1answer
16 views

Which type to pick for column that will hold a variety of data types

I'm setting up a Postgres database that will function as a storage place for data records. There's a column in the records that I'm going to be inserting that contains different kinds of data types; ...
1
vote
2answers
32 views

Optimal way to save Social media posts from multiple platforms

I am working on a platform, that gathers post form multiple sources(Facebook, Twitter etc) and saves them to the database. The plan was to create one table called posts with all the necessary fields ...
1
vote
1answer
29 views

SQL Temporal Tables Include Current State

We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables. We noticed that history tables only include history. ...
0
votes
1answer
32 views

SQL Temporal Tables Begin and End Date

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables. For our BeginDate and EndDate, we want them to be date,...
1
vote
1answer
32 views

Use extra column to save an unaccented copy of a string instead of using unaccent module [closed]

I am heavily using the unaccent module in a table with 500.000 rows where each row contains proportionally a lot of accented characters. The issue I am facing is that my queries have become time ...
1
vote
0answers
12 views

SQLite database: many-to-many optimization (simple but large example) without IDs available?

I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of ...
1
vote
0answers
16 views

Coffee shop static tag/attribute querying (satisfying AND & OR condition groups) - Relational DB/DBMS or NoSQL?

Let's say you are storing information about coffee shops, and you want to assign an arbitrary number/assortment of static tags/labels to each cafe which act like attributes (i.e., the cafe either has ...
1
vote
2answers
29 views

New Table vs Adding Columns To Existing Table

I have a table, products with the following columns: id, name There is another table, countries with these columns: id, name, code The products have different prices across different countries (same ...
2
votes
0answers
27 views

Modeling Database to Support Sub-Users for Users [closed]

I'm having a hard time wrapping my head around how to best model my relational database. I want to be able to structure it in such a way that a User on my site can create Sub-Users that are able to ...
0
votes
1answer
24 views

Single or multi tenancy database recommendations

I'm really confused searching for a database(nosql or mysql) that can handle unlimited multi or single tenancy with good scaling method either by software or physical resources. So i can differentiate ...
1
vote
1answer
24 views

Looking for tree organisation for database, based on one to many relationship both ways [closed]

All the tree organisations for databases I've found documentation, papers or blogs about were built on a one parent node can have many children, but a child node can have only one parent, with a ...
2
votes
1answer
88 views

Creating a Friendships database that includes a Block Friend option

My database teacher asked us to program a clone of Facebook using MySQL as the database management system, so I'm trying to represent a friends relationship in the corresponding database. So far I ...
0
votes
0answers
30 views

Understanding “fully functional dependency”

I'm having a bit of trouble understanding fully functional dependency (FFD for brevity). So imagine a scenario where there's a table describing bills for a resturant. The table has the following ...
0
votes
0answers
30 views

Design a database for multiple similar server data [duplicate]

I have to design database on a digitalocean server for storing data from a plant(data source). Each plant needs 10 tables for storing its data. Once all the previous data(past 1 year) has been ...
2
votes
1answer
38 views

Database design for a simple association

I'm not so good in db/tables design, I'm mainly a frontend web developer, but sometimes I do really small apps. Always I find myself wondering if my thinkings are correct.. So I try to ask here :) ...
-2
votes
1answer
65 views

About Foreign Keys that is not a primary to other tables

I'm clearly new into database implementation and I'm having doubts about my keys. I have about 5 tables namely: User, Profile, Client, Stylist and also Posts sirs. I have a certain foreign key named ...
0
votes
1answer
25 views

Advantages of a hierarchical schema for private chat

This post claims that All modern chatting interfaces, without exception, implement a hierarchical and a not-chronological schema for chat. It also links another post which gives an example ...
3
votes
0answers
34 views

Third Normal Form (3NF) Decomposition Scenario

I am trying to understand what are the steps to check if a decomposition of table is in 3NF. There is a specific exercise I'm trying to solve with no success. Given that R={A1,A2,A3,A4} and F={A1→...