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
3answers
102 views
Who guarantees the data integrity of a denormalized table, me or the DBMS?
In the following database, School_has_Student is a denormalized table :
Now if I decided to change the "paul" student name to "john" in the Student table, then the "paul" student name that exist ...
1
vote
4answers
122 views
When denormalizing a database, do I need to keep the parent tables?
Say I have the following ER diagram:
To implement the above ER diagram as a normalized database, I would create a School table, and a Student table, and a School_has_Student table (that only contains ...
-1
votes
1answer
33 views
Databse model: Entity that can have values from two different tables or user input values
I'm modeling a database, where I have a table called "appointments" (citas in spanish, but I would translate the tables's names for better understanding) where the user can create an appointment to ...
0
votes
0answers
30 views
denormalization and streaming of database data
Our use case:
We have a Sybase database with around 20 tables, they are of different granularities but there are relationships between them meaning they can be joined.
Aim is to build a denormalized ...
-1
votes
3answers
97 views
DB architecture for managing items of different categories
I'm making a rental app which has many categories with unique properties
Example:
Category 1
Car which has
Fuel, model, color, speed
Category 2
Apartment which has
Rooms, lift, ...
0
votes
1answer
28 views
Watchlist polling API NoSql vs Sql Frequent Updates
I am making a watchlist type application that will get all items being watched and then poll an api every ten minutes or so to check on its status.
I am trying to design the database portion of this ...
3
votes
4answers
128 views
How are account balances persisted
I am currently building a finance application that contains "accounts"
Each account has a balance that is dynamically generated based on platform charges vs payments. e.g simplified example.
$...
2
votes
2answers
162 views
How to design a relational database where users can create custom fields
Ive been looking around for a way to design a database for large amounts of leads (users) with custom fields.
Ive check this out (How would you design a user database with custom fields), but this ...
0
votes
1answer
24 views
How to resolve multiple user serve on multiple location appointment booking system
I'm trying to build an appointment system but at one point I stuck
Problem:
I define available slots for the next 7 days with help of a cron.
I have multiple users serve service more than one ...
0
votes
1answer
38 views
Descriptive short term for a field that has been set to read-only in anticipation of removal?
I'm currently writing a design to add the possibility to mark a field or attribute for an entity as read-only in preparation for imminent removal. The idea is that, once the field has been thus marked ...
-1
votes
0answers
26 views
Is-a of an aggregation - entity relationship model
I'm doing a school assignment where I have to create an entity-relationship model given a text and, to design a specific feature I think I'd need something like the specialization of an aggregation, ...
0
votes
1answer
46 views
Calculating time an object stays in a state
I use Python sqlalchemy to store my model in a MySql database. One of my objects has a 'state' field (for simplicity let's assume there are two states: AVAILABLE and UNAVAILABLE).
There are ...
1
vote
1answer
29 views
Representing classes hierarchy in database (JPA Inheritance)
I'm working on staff management application for a clinic, basic scenario, but learning about JPA inheritance lead me to over think some parts, and I need help to clear my thoughts and design.
...
3
votes
2answers
163 views
Is using -1 as a special Id a good idea in SQL?
I'm trying to determine if using '-1' in an Identity column has any drawbacks, or if there is a better way to do what I want.
In my database I have User table and a Group table. All users must belong ...
2
votes
1answer
33 views
Best practices for app-cloud synchronization of database representation
I'm planning a cookbook-like Android app that would let users store/add/edit their e-juice recipes, keep track of the ingredients, etc. Internally, I'll be storing the data in a database on the user's ...
2
votes
2answers
99 views
How to store sequential timespans in a database?
How can consecutive periods of time (defined by a start and end date) be stored in a database?
For example:
from 2018-01-01 00:00:00+01:00 until 2018-06-01 00:00:00+02:00
from 2018-06-01 00:00:00+02:...
2
votes
2answers
113 views
How to design ID code system for people database?
We are restructuring our entire database / filesystem / user identity system. As a first step, we have determined that we need to assign each user/employee in an organization unique ID. Surprisingly ...
0
votes
0answers
32 views
Time series database for low-resolution data
Does it make sense using a time series database, such as opentsdb, for very low-resolution data (hourly, daily) or I should rather use a relational/NoSQL database engine instead?
It's quite possible ...
1
vote
3answers
63 views
Database schema for point-in-time data
What’s a good strategy for storing point-in-time data in a relational database?
For example, let’s say you have products and orders. Every order has some products. But they’re not the products as ...
1
vote
0answers
34 views
separate table for one of the type or java enum?
I want to make budget tracking web-app, tracking all savings and expenses with corresponding dates. Later generating reports, summaries etc..
I came up this design
Frequency Table - does it have to ...
3
votes
3answers
289 views
One Database for Multiple Applications?
Has anyone worked in an environment where multiple in-house applications are all under one database with each application given its own schema within that single database? I don't just mean a few ...
3
votes
4answers
94 views
DB Enumerations vs App Enumerations vs Lookup tables
We started a project recently, and during the design process, we have some arguments among team members. We have to deal with enumerations so we ended up with three choices.
Have the enumerations as ...
1
vote
2answers
237 views
How to process huge amount of data with limited processing ressources? [closed]
I receive daily from an external source a very large amount of data (around 250GB with 260 million rows of fixed width text) distributed over 5 text files. I am writing a Java application that should ...
-1
votes
1answer
63 views
What is the best Architecture / System Design to launch same app in different flavor and a bit different logic?
There is a company let's say "X" which mainly deals in Real Estate / properties and has a website (online portal) to display those properties.
Just like any other big company when they saw a ...
-3
votes
1answer
81 views
Database design for online applications
I am developing an Online Application portal. But I am stuck in designing my database tables, and am unsure about the best approach to take.
Background: how the portal is supposed to work
Let me ...
2
votes
3answers
148 views
How should I implement custom database fields?
I'm building an app where end users can add custom fields to database tables. Think of it like a CRM app, where sometime you just need to add an extra field to your customer table. Pretty much all ...
1
vote
3answers
51 views
Is this implementation for the “one” side of the relationship correct?
Say I have the following ER diagram:
The "one" side of the relationship of the above diagram means the following:
Each student can meet 0 or 1 teacher.
So I can't do something like this:
But can I ...
1
vote
3answers
173 views
Is there any risk to having row IDs visible to users?
In this article, blrfl makes a good case (How to design a good receipt number) for keeping things simple. Don't encode information into numbers - any information needed can be looked up. I am very ...
0
votes
3answers
172 views
Does the “one” side of the relationship also apply to the same record?
Say I have the following ER diagram:
What I know is that the "one" side of the relationship means the following:
Each student can meet 0 or 1 teacher.
So for example student3 can meet teacher7, but ...
1
vote
1answer
49 views
Better to store miscellaneous metadata in database or calculate on each access
I have a number of attributes I need for various page loads and other backend tasks, and I'm debating on whether storing these things in a database or calculating them on the fly.
For instance, if ...
0
votes
0answers
20 views
mongodb/mongoose - ensuring uniqueness between two lists
So I currently have my list and item collection set up (mongoose).
List
new Schema(
{
items: {
index: true,
ref: "items",
type: [ObjectId]
},
...
-1
votes
2answers
57 views
The challenges of removing/modifying entries from a database located in persistent storage
I'm implementing my own simple database with disk storage, and I'm not sure how to go about modifying and deleting entries.
The problem is that as you delete a record from arbitrary position within ...
0
votes
0answers
29 views
How should I store multiple, related models when configuration control is required?
My problem: I plan to store a "project" in a database, where a project is composed of multiple items e.g. documents, and each document has multiple items e.g. paragraphs. Paragraphs may cross-...
-2
votes
1answer
58 views
Relational Database Design with a table having redundant information
I am creating a database table that will store a single number 1 thru 16 in 2 fields (there will be many rows of data).
I'm trying to avoid duplicating a lot of data and make my dbase most ...
0
votes
1answer
95 views
Express unique recursive task system
I would like to express a task as either itself or a container for other tasks (recursively). The problem is that each task has to be one of two fundamental types: a goal or a routine. And, children ...
1
vote
1answer
63 views
How to organize users data in NoSQL database for a user invite feature (even before a user sign up)?
I am doing a collaborative note editing app which uses a NoSQL database (Firebase) in server side. Each user can sign up using their phone number or email. Currently, the user object has only four ...
0
votes
1answer
29 views
Extending photo album app with videos
I have a personal photo album application where I upload my photos. However, I'd now like to extend it with videos as well, and I'm unsure how to properly perform this extension.
The current design ...
0
votes
2answers
124 views
How to create a db schema for “if this then… elseif … elseif …” and so on?
I want to create an app where a user will go through a series of steps every day. All the subsequent step will depend on the previous ones, that is, 2 users can have different experiences depending on ...
2
votes
3answers
205 views
Logical separation of database content
Background
I have an application which stores a lot of entities in a classical relational database (Microsoft SQL Server) and I use an ORM (Entity Framework) to query data from it. This database has ...
0
votes
0answers
21 views
How to implement dynamic settings in a web app — db table design?
In my web app I want to be able to use Settings as an admin that I can edit/remove/add via my admin page
I think I'll create a table like this:
settings(id: int, key: string, value: string)
I ...
1
vote
2answers
607 views
Why should I use foreign keys in database? [closed]
In my 10+ years of experience in the IT field, I have never used foreign keys in any of my project and I never felt the need. I did work with professional databases which had foreign keys constraints. ...
0
votes
0answers
107 views
Ontology optimization
I have a pretty complex ontology of an application which is used for decision making. By ontology here, I mean basically how entities are related and how they are composed and structured (the context ...
1
vote
1answer
78 views
System design for credit calculation usage
We are implementing a system where a user can purchase 'credits' to be spent on two items. I am hoping for suggestions on how to implement such a system. We have two ways of spending credits: ItemA ...
0
votes
0answers
46 views
Polling Postgres DB architecture
We are using Spring Integration DB polling which polls a table every second and executes some logic based on result. My questions are:
What are some good practices we can implement to account for DB ...
0
votes
0answers
21 views
Implementing Products, Purchases and Payments and Statuses
I have Products, Purchases and Payments.
I'm trying to figure out how to deal with Payments and statuses and the ultimate status of Purchase - "paid", "failed", "refunded", etc....
My requirements:
...
2
votes
3answers
192 views
Database design for generic behaviour
I have a few tables that are generic. One of them allows a user to comment on something, and the other one allows a user to favorite something.
Initially, I had thought that to make it possible to ...
-1
votes
2answers
69 views
Should the TotalGrandPrice be property or Order or Invoice
I'm developing a shop. I'm trying to figure out: where should "total_grand_price" belong to?
"total_grand_price" =
price of all order items
- discount
+ shipping
+ taxex
+- etc....
Should ...
4
votes
2answers
305 views
Need to email customers once per year - best way to record this
We need to email our customers once per year (yes it's opt-in etc).
As I see it we have 2 ways to record the fact we contacted them (so we know not to contact them for another year). A log, or a '...
1
vote
0answers
215 views
Database design for a subscription service transactions
I need to create a table or a set of tables that needs to house transactional data of a subscription-based service, so that certain metrics can be queried efficiently paired with necessary post-...
1
vote
1answer
74 views
What are good ways to encode order between records of a relational table
For example, when an issue tracker offers the functionality to users to keep a prioritized backlog of tasks and used a relational database for persisting this information, what are effective ways to ...
