Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,329
questions
0
votes
0
answers
25
views
What would be the name of this pattern?
I have a match entity that represent a tennis match, said match can be between two players or two teams. You cant have player vs team, and a player can be in many teams. I've decided to implement this ...
0
votes
1
answer
17
views
Transactional replication Snapshot agent not starting
I am trying to consider all the options before setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL ...
- 13
1
vote
1
answer
27
views
Need help in rewriting query
I have 2 databases test1 and test2. where I need to compare columns from schema1 to schema2 and fetch the results.If table in test1 schema not present on test2 schema ,it should ignore that table. I ...
- 11
0
votes
1
answer
30
views
Breaking Down Transaction Table
My table transation consists of tranxid, custid, itemid, quantity, unit_price, payment mode, driverid.
When I perform add operations and notice that customer 1 can buy at least one or two items more ...
0
votes
1
answer
24
views
Cannot connect postgresql server
i ran some error today with my postgresql maybe someone can help. I install Postgresql in windows server. I connect using PgAdmin. Usually i just enter my master password, then the next step i enter ...
0
votes
0
answers
54
views
Is it bad practice to have tables inherit from a parent table to reduce the number of join tables?
In the database I'm building I have many entity tables (users, videos, books, games, etc, etc) that can all be related and potentially have lots of additional data attached to them (comments, links, ...
- 101
-3
votes
1
answer
44
views
Are there mnemonics in the database design space that you find helpful when communicating database design concerns?
A popular and useful mnemonic in the software development space is SOLID.
Single responsibility
Open–closed
Liskov substitution
Interface segregation
Dependency inversion
Are there mnemonics in the ...
- 135
0
votes
1
answer
38
views
Master Data Tables
I have a table called Pricing.
Item x 10
Itemid itemname itembrand
Customer X 20
Custid custname custdetails
Pricing
pId itemId custId price
If itemId has 10 records and customer has 20 records. Data ...
-1
votes
1
answer
40
views
What is the scope of Java developer in future? [closed]
I need suggestions and scope of java developer
0
votes
1
answer
89
views
Is it really possible to use SQL injection to change a database, or is this a scam?
A guy I know at college is claiming he can change his grades by gaining access to the database through an SQL inject, and can also gain access to all admin account privileges and records. The portal ...
- 11
0
votes
0
answers
40
views
Database model for multi-channel notification system
We're currently redesigning the way our application sends out notifications to users (so far, it has been mainly calls at various places in the code, directly sending mails to the user) to a system ...
- 101
0
votes
1
answer
48
views
How to build an efficient EAV model over JSONB in Postgresql
Context
Hello, we have a project we integrate with a ton of different CRMs. That means that when a new client comes in, we get all their data from their CRM and store it in our own postgresql database....
0
votes
1
answer
47
views
How should I differentiate between system-defined and user-defined data?
I'm using PostgreSQL and designing a relational database schema for an application where I want to store both system-defined (built-in) data and user-defined (custom) data in several tables, e.g.:
...
- 65
0
votes
0
answers
25
views
ER modelling for bus company
**
A bus company owns a number of buses. Each bus is allocated to a
particular route, although some routes may have several buses. Each
route passes through a number of towns. One or more drivers ...
0
votes
1
answer
48
views
How to structure a database with multiple 1-N relationships?
I'm looking for advice on how to model the tables for the following entities. The main concept is a Job. There are two Job types, regular ones, and case studies. This question is about case studies.
...
- 123
0
votes
1
answer
62
views
Deduplicate large set of small strings (or hashes)
I have, eventually, around 10-20 billion strings of a length of 10 ASCII characters which I'm trying to deduplicate. The values are distributed quite nicely between the starting character as it's a ...
- 101
1
vote
0
answers
29
views
Remove Transactional Replication Manually or Via SSMS?
I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 ...
- 37
0
votes
0
answers
61
views
Modeling hierarchical data for multiple entities, inherited properties, and varying hierarchy
I am trying to model hierarchical data using a RDBMS but can't quite find the right solution. We are capturing information about storage levels in a warehouse system. There are 8 possible storage ...
- 1
-1
votes
1
answer
65
views
SQL: Entity that belongs to either one or another entity
I have a question about a scenario similar to the one described here:
SQL: Entity belongs (many-to-one) one of two different entities
Let's say that I get the need of the Party table and all the other ...
0
votes
1
answer
39
views
Bad habit tracking database schema
I'm building an application to track negative habits. I came up with the following database schema
+-------+---------------+-----------+
| Users | Habits | HabitType |
+-------+---------------+-...
- 169
0
votes
1
answer
41
views
Designing a schema with non-enumerable, unpredictable public ids in MySQL
I'm building a REST API, backed by MySQL. Normally for every table I will have at least one: id UNSIGNED INT NOT NULL PRIMARY KEY AUTO_INCREMENT field as a default, but the issue with this is that it ...
- 151
0
votes
1
answer
62
views
What impact has uniqueness constraint jsonb on postgres db performance
I'm designing a postgres database in which there is a table that has a jsonb type column. I would like this column to be unique. There is no need to have two objects with the exact same json ...
- 103
0
votes
0
answers
43
views
Simplifying Complex Traversal Patterns Involving Multiple Association Types (1:N, M:N, N:1 ...)
I'm working on a data model for a system that represents interconnected entities with complex traversal requirements. Specifically, I have the following scenario:
An EntityA connected to multiple ...
- 1
1
vote
1
answer
36
views
How do model different logical views on data
Simplified example of a data modeling problem that I have:
I have a domain model with an image table, a camera table, a machine table, a foreign key constraint between image and camera (...
- 13
0
votes
1
answer
39
views
Using internal or external ids when storing external data
When storing external data, I usually have two ids. One is external and one is internal. I inevitably need to keep indexes on both ids for lookups. Examples:
Storing stripe customers, products, ids
...
- 109
0
votes
1
answer
36
views
Manage the amount of data on tables
I can have several tables with 10+ millions rows in by year.
I would to know what's the best approach to maintain responsiveness with this amount of datas and I have two ideas.
Don't touch these ...
- 1
0
votes
2
answers
47
views
Can you have a minimal transactions data warehouse?
Good Morning,
I have a client doing scientific research. The current database (Azure postgres) design is 3NF but very few transactions are happening. There are several "pipelines" that add ...
- 103
0
votes
1
answer
26
views
Designing a data model to handle both recurrence and nested dependencies
I'm building a web application that involves both recurrence and nested dependencies, and I'm struggling to pin down the appropriate data model.
The problem that the application aims to solve is: we ...
- 101
0
votes
1
answer
28
views
Mysql problem not valid syntax
reason:Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'почта VARCHAR(50), тип травмы ENUM('...
- 1
0
votes
1
answer
38
views
Questions get answered by a user, how to store historical data alongside the answer?
I'm building a database where a User can answer Questions, each Question belongs to a Category.
Now what if someone changes a question or category name later, I need to store the historical data of ...
- 169
1
vote
2
answers
133
views
Efficient way to store and retrieve big amount of data
I need to store a big amount of data (about 2.5 billion of new data rows per month), but also I need a very fast way to retrieve the latest value per group on specific time point. The data looking ...
- 11
0
votes
1
answer
24
views
ideas to reduce association tables while building a data warehouse
I'm trying to create a data warehouse for my BI project. Initially I have a raw database that contains 100 data tables, most of them are association tables. My goal is to create normalized dimensional ...
1
vote
1
answer
19
views
Multiple parents for AspNetUsers table for Asp.Net Core 3.1 app
I have the following table in MySQL.:
CREATE TABLE `aspnetusers` (
`Id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`UserName` varchar(256) CHARACTER SET utf8mb4 ...
- 11
2
votes
2
answers
216
views
Transactional Replication - how to set up alerts/notification to send alerts before transaction log space is full on a Publisher?
I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 ...
- 37
0
votes
3
answers
49
views
in Relational database, will index accelerator query: select count(*) from table where score >= number
Let's say we use MySQL
With 100 million records
Table schema: userid(pk), score
score ranges from 0 to 10^6
score has duplicates
So the question is when adding a index on score, will it accelerate ...
- 11
0
votes
1
answer
28
views
How to reference uploaded blob objects in database tables
When there are urls in a database table that represent media files uploaded to my own blob storage:
Is it common to link a column relationally to an objects table
Ex:
videoHdObjectId: "XXX"...
- 109
1
vote
0
answers
9
views
Design: How to store boolean values for entites whose name may change?
Let's say I want to design a DB for an FMCG Manufacturer - and they have several business units, and several factories.
BUs: Beauty, Homecare, etc.
Factories: SiteA, SiteB, etc.
Now, I want a table ...
- 111
0
votes
1
answer
33
views
Question related to Database Design in SQL Server
I have two tables: Products and ProductVersions. The table structures are as follows:
Products Table:
ProductId
RegionId
ActiveProductVersionId
isDisabled
ApplicationId
ProductVersions Table:
...
- 527
0
votes
0
answers
41
views
How to model transliteration (and translation) in a database?
I am working on a project in Django (backed by Postgres) to store manga titles, that will have values that should be translated and transliterated. Since I am starting from scratch, I figured my ...
- 101
0
votes
1
answer
62
views
Setting up Transactional Replication - Can i safely remove transactional replication if it is causing issues on my publisher?
Please excuse me if I am not making very much sense here. I am very new to replication and have been researching for quite some time about transactional replication to implement in our scenario..
I am ...
- 37
0
votes
0
answers
21
views
Linking records through multiple tables?
I'm writing a game server. Similar to the Sims. Players can own furniture items they purchase from the catalog. There can also be rooms. Players can be in a room. A room can have furniture items of ...
- 121
0
votes
1
answer
16
views
Chen ERD: What improvement could I make? Can relationship `RECEIVES` be omitted since entity `INVOICE` is a weak entity of `CAR`?
Assumptions:
A salesperson may sell many cars, but each car is sold by only one salesperson.
A customer may buy many cars, but each car is bought by only one customer.
A salesperson writes a single ...
- 1
0
votes
3
answers
131
views
Designing Tables for Large Amounts of Time-Series Data
I'm designing a Postgres table to store large amounts of time series data and I'm trying to figure out the best way to structure the columns. I've looked at answers such as this one, but since it's ...
- 123
0
votes
0
answers
32
views
Looking for fast large-scale image embeddings comparison database structure
I plan to store 1-2 million of entities into a database. Each entity will be linked to 10-100 image embeddings. The goal is to use an external image that may or may not be part of the database and ...
- 101
0
votes
5
answers
154
views
MySQL solution for many to many associative table that scale over billion entries
Scenario
Imagine we have a table user and an item user.
These 2 tables have an associative table called user_item to define a many to manyrelationship.
We start 100 item records
We have 500 Millions ...
- 151
0
votes
2
answers
36
views
SQL Database - how to design entities to make search operations faster?
I need to design some database entities to store logged events.
The events have several properties, among which the message. This message is actually coming from a limited set of templates, with ...
- 101
0
votes
0
answers
44
views
Additional Information Required During Requirements Analysis for Database Design
I am a student currently Studying in Financial Technology in Kitchener. so I have an academic assignment focused on database design, and I need some expert advice. As part of my assignment, I need to ...
-1
votes
3
answers
301
views
How are columnar databases faster for querying?
Why do so many people prefer columnar databases?
To my understanding, people prefer columnar databases because they are faster for querying due to the fact that the cpu would be able to cache the next ...
- 109
1
vote
1
answer
51
views
Unique constraint for single structure that simulates relations between tables
Imagine I have several tables in SQL Server 2016:
Cat
Cat carrier
Cat transit box
Cat car
Every object can be stored in higher level, but not necessarily directly one above with N-to-1. So a cat can ...
- 11
0
votes
2
answers
62
views
How to calculate revenue by sales team if sales agents change teams?
Agents
ID | Agent
1 | A
2 | B
Teams
ID | Team
1 | AA
2 | BB
Memberships
ID | AgentID | TeamID | Start Date | End Date
1 | 1 | 1 | 2023-...
- 99