Questions tagged [database-design]
For questions about structuring the data within a database. How to lay out tables, whether to use a relational DB or not, etc.
0
votes
0answers
3 views
Application/Database design where two tables have both 1-to-many and 1-to-1 relationships
I have an application where the requirements are to show a list of (just for example) car manufacturers. When you click on one of the manufacturers, a list of car models show up. You can then click on ...
0
votes
0answers
57 views
At what point in design do you create a new database or just keep adding tables [on hold]
My question is at what point should you create a new database and why?
So after many years of creating small projects I have come to the realization that I have way to many db's. Now it's not a ...
0
votes
3answers
89 views
Could you please recommend an approach on converting non-unique field to a unique field?
On our prod db we have two tables:
companies
invoices
Relationship:
A company has many invoices
An invoice belong to a company
The invoices table contains duplicate invoice_number for each company. ...
0
votes
0answers
36 views
Designing database relationships for a voting application
So I designed database relationships for a voting/polling application. I want a user to only be able to pick 1 option on a poll. I want multiple options per poll.
So what I have here is this.
User
...
1
vote
2answers
50 views
Branching SQL relationship to C# object model
I'm developing a C# WPF desktop application where I need to read/write to an SQL database regularly. Now I want to map the data from the database to objects in C#. I can't use Entity Framework so I'm ...
41
votes
7answers
7k views
Is domain driven design an anti-SQL pattern?
I am diving in the domain driven design (DDD) and while I go more deeply in it there are some things that I don't get. As I understand it, a main point is to split the Domain Logic (Business Logic) ...
1
vote
0answers
21 views
DataBase design generated by hibernate Many to Many for MariaDB
Hi I am in middle of designing DB and Got a little bit confused about DB Design.
I am Using Hibernate as ORM Tool ahd mapping between Page and Step entity in hibernate.
@ManyToMany(fetch = FetchType....
0
votes
4answers
54 views
Extent of Administrator Capabilities: HR and Design Logistics?
So I am developing an employee management application on behalf of a company and am concerned about the extent of rights and capabilities that are to be bestowed upon registered administrator accounts....
0
votes
3answers
73 views
What are criteria for enterprise to have multiple databases (for new project)?
We are working on new enterprise project (multiple activities - online ordering, offline ordering, b2b ordering, accounting processing, secure payment processing, analysis and dynamic pricing and so ...
2
votes
1answer
107 views
PHP - Dynamically apply parsing rules to text, and save to dynamic fields
OK, so I have been at this problem for the last few days, and I must admit - I am stuck.
I am trying to make a web application, where users can upload documents or send inbound e-mails.
Each ...
1
vote
1answer
117 views
PHP - Designing a rule based parsing engine
Dynamically apply methods/"rules" to documents
I hope someone here can help me/guide me in the right direction. I am currently creating a web application, where users can import a text file, and then ...
1
vote
2answers
114 views
Multiple use/semantics of the same database fields - is it acceptable practice?
I have ERP database whose good or person entities each have several hundreds of fields. Many of them are used for some subjects only. And hence senior companions (especially from the business division)...
6
votes
3answers
330 views
How to model public IDs?
While creating the architecture of a new project I struggle to come up with a good solution to handle public IDs.
For example our users will receive a membership card. Each card has the membership ID ...
2
votes
2answers
71 views
How to implement change log of a ticket in complaint management system [closed]
One of the requirement, in the complaint management system were are designing, is to implement complete history of the ticket. For example
Status was changed (new to pending to work in progres to ...
3
votes
2answers
131 views
How to keep logs of emails sent by application
We have an ecommerce application which sends out transactional emails to customers for events like order placed, order shipped, order delivered etc. We used to send one bcc of every email to a gmail ...
0
votes
2answers
98 views
Some Suggestion about Data Modeling
I want to make a social game for Android. I am using NoSQL Based Database (MongoDB) and I am using NodeJs. I am using Android-Volley library to make POST and GET requests. But I am stucked on ...
-2
votes
1answer
44 views
Preuso/empty entity or only real entities + nulls?
I have a table "products" and a child one "product_modifications"
Some products have "product_modifications": "colour", "size",, .... But not all.
When a client orders a product, views pictures and ...
0
votes
1answer
62 views
How to create a flexible datamodel?
I'm trying to create a flexible datamodel and i'm not sure what's the right approach.
My application is used by several clients (big and small). Big clients need all the seperate layers, but small ...
0
votes
2answers
96 views
Referencing Database Primary Keys from Codebase
I don't have a lot of experience with these kind of issues, but I feel I need to consult on this issue. The current codebase I'm working on is using what I consider to be a questionable technique to ...
1
vote
1answer
89 views
DDD many-to-many relationship on single entity
To provide a bit of context, the system I'm working on is a football database. A Squad has a collection of Rivals. Arsenal (Squad) has Tottenham and Chelsea as rivals, where Chelsea has Tottenham and ...
2
votes
4answers
142 views
What is the name of DB design pattern which support only soft-deletes and soft-updates?
I heard that there is some DB design in relational databases where updating a record will create a new record with different timestamp or status. Also, deletion will be soft-delete (it updates the ...
3
votes
1answer
98 views
Auto incrementing from two different points on the same table?
Each customer has an ID. For every new customer it's incremented by 1.
My company is wanting to start selling to a new type of customer (lets call it customer B), and have their ID start at 30k on ...
10
votes
5answers
557 views
Do I really need triggers for relational database, for example, PostgreSQL?
I know that triggers can be used to validate stored data to keep database consistent. However, why not perform validation of data on the application side before storing them into the database?
For ...
3
votes
2answers
97 views
How to design a multi-year statistics database?
I'm designing a database for name statistics (how many people where given that name). The data consists of names, and numbers for how many men and women were given that name within a time period (...
0
votes
1answer
44 views
Database design for a survey app [closed]
I'm creating a survey app and I don't know how to design database. I need surveys with multiple questions and multiple types of questions. There are 3 ways that I could think of:
a) Create a ...
0
votes
0answers
31 views
Alternatives to RDBMS For Forecast Data
All,
I currently capture well structured forecast data in a SQL Server. An example of this data is below:
What this tells me is that each day, I receive a forecast for the next three days. Note ...
-2
votes
1answer
47 views
Designing a facial recognition systems database
First of all, I hope im in the right stack exchange.
So I'm trying to build a facial recognition system, e.g. one that recognizes a face and compares it to a database of known faces. For the first ...
1
vote
0answers
46 views
Strategies for dealing with Stale data and Fetching
I'm working with firestore and have encountered several situations where a document will contain stale data which is a duplicate of the source of truth located elsewhere in the database.
For ...
0
votes
1answer
34 views
In a parent table - Child table relation , where to store order of children
Assume a Parent -> Child relationship in RDBMS.
questionnaire table and question table.
question table has a foreign key questionnaire_id to store which questionnaire it belongs to.
Questionaire ...
0
votes
0answers
64 views
What should I do if I am unfamiliar with my organisation's codebase? [duplicate]
I've recently started my first job as a software developer at a small startup company.
I do not have a degree in a software engineering related field, although, I have very recently completed an A-...
1
vote
0answers
24 views
Test Results Database Design
I am trying to write a database to store test results for components, against a serial number, with different components potentially having different tests. I want to prioritise ease of adding new ...
1
vote
1answer
63 views
How/when to normalize during ETL?
Let's say you're loading a denormalized flat file of purchase transactions that looks like this:
| location_name | location_zip | product | product_price |
|---------------|--------------|---------|--...
3
votes
6answers
290 views
Should I use entity framework for CRUD and let the database handle the complexity that comes with high end queries?
I am new to ef and liking it since it reduces the overhead of writing common queries by replacing it with simple add, remove functions. Agreed.
Today I got into the argument with my colleague who has ...
0
votes
2answers
96 views
Should I make a new table when a column only is populated when another column has a certain value
Lets say we have some configurations stored in a database for how we should acquire some data. One of these fields is an AcquisitionStrategy which denotes how we will acquire the data.
I want to add ...
1
vote
1answer
51 views
Regarding data modelling in a NoSQL use case
I am reading the following link to learn about system designs of various systems. https://www.educative.io/collection/page/5668639101419520/5649050225344512/5673385510043648
(This is a paid link, I am ...
-3
votes
2answers
249 views
Database Schema for a multiplayer quiz game
I'm trying to accomplish this scenario :
There are 2 types of users, let's say Admin and Worker, and there have different roles.
Admin can do a CRUD of questions, and also can create a room where ...
2
votes
4answers
129 views
Database management good practice - should it be in code or with database tools?
What is good practice of settings up a database in potentially large project - creating tables and updating them - should it be done in code of the app or should it be done by external database ...
1
vote
1answer
45 views
What is a good way to add extra info to data-entries from a website front-end?
I want users to be able to dynamically add 'columns' from the front-end of the website. I understand that it is probably not best practice to actually add columns to a table from the front-end, so I ...
1
vote
3answers
93 views
Database relation design
When I develop apps I reach situations like this frequently but I never found a best practice to solve it.
Imagine:
We have chats, each chat can have many message.
We have tickets, each ticket can ...
-1
votes
1answer
37 views
TEXT column as key for a table when there are other tables referencing that table?
I have a UNIQUE TEXT column (“name”) in SQLite for one of my tables (let’s call it “table A”) and i’m considering using that column as the key for the table
The reason i can see for continue using a ...
0
votes
1answer
144 views
My MySQL database architecture for ecommerce website
I'm making an ecommerce site, using MySQL for storing data. Here is my ERD:
CREATE TABLE `admins` (
`id` int(10) UNSIGNED NOT NULL,
`shop_id` int(10) UNSIGNED DEFAULT NULL,
`name` varchar(75) ...
3
votes
2answers
72 views
Approach for storing lots of data of unknown type in database
I have an web app to which external applications can be connected to send their data. This is for telemetry purposes.
The web app is not controlling what kind of telemetry data is sent to it - it can ...
1
vote
1answer
52 views
Using UNIQUE (col1, col2) or using a composite primary key for a relation table in SQLite/SQL?
i’m working on a project where i’m using SQLite and where i have a few relation tables looking similar to this one:
CREATE TABLE tag_entry_relation
(
id INTEGER PRIMARY KEY,
tag_id INTEGER REFERENCES ...
2
votes
2answers
71 views
Index / Unique on `deleted_at` column?
We have an Accounts table in a Postgres DB that has a unique index on user_id and product. Users can have many accounts, there's no product table. Occasionally users will create an account ...
2
votes
0answers
27 views
Designing provisioning strategies
I'm trying to redesign a provisioning application that administrates other application through their REST API.
The application is quite simple, we have groups, we have users and roles and we have a ...
3
votes
3answers
263 views
Aggregation of data from two Microservices
I have two Microservices A and B.
B Microservice has a large set of an entity called User.
A Microservice stores the User entity in its own DB if User is configured by an agent. There is no flag ...
1
vote
1answer
69 views
A table for each field type or one all-in-one table model?
I am creating an application where users can create forms with many fields of text, numeric, date and select types.
The model will have a forms table referenced by responses table;
It also have the ...
1
vote
1answer
175 views
Designing SQL table for prices changing in time
I'm developing a database with a price list table for my workplace. I came with two major options, and would like to have your opinion :)
It's rather simple, containing: Id, description and price
...
2
votes
3answers
514 views
Database : Does it make sense to choose the zipcode as a primary key for an address?
I was following a tutorial on the normal forms of SQL databases, and I got confused landing on this example : https://www.tutorialspoint.com/sql/third-normal-form.htm.
From
CREATE TABLE CUSTOMERS(
...
2
votes
1answer
70 views
Proper way to associate data to users that are stored in external database?
I have this IdentityServer which handles authentication and authorization. It saves users, roles, claims, etc in its own database.
Then, I have a web app that needs to protect some resources to ...
