Tagged Questions
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→...
