Questions tagged [database-design]

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

Filter by
Sorted by
Tagged with
0
votes
2answers
28 views

Is there a good reason to put the company name at the beginning of the table names? [on hold]

When developing a new system, for a big corporation. Technologies: Web, Java, Spring Boot, Rest Api, Postgres, Hibernate Company: Bank System: Promissory control, and all others Company name: ...
0
votes
0answers
11 views

P2P message system design

Database noob here, just curious to see if my design could benefit from any improvements. My goal was to keep this as simple as possible: a user can create an account, two accounts can message each ...
0
votes
0answers
27 views

Designing database without knowing the structure

I am looking for a bit of advice about designing a database when I am not sure what data is required. I am using mySQL. To provide a little context, I am building a reporting system. So to start ...
1
vote
1answer
20 views

Database design for bidirectional relationship

I am modeling a bidirectional relationship between users with a relationship table. Each relationship deals with two and only two users. Each direction of the relationship allows for 3 different ...
1
vote
0answers
25 views

Database design for a customer and payment system

I need help with database schema design. Business rules We have users, each user can have listings. Each listing has one or more license. There are n type of licenses (products) Each listing can ...
0
votes
0answers
43 views

Assistance with appropriate schema for this dataset involving the services of parcel couriers

I would like some assistance with the appropriate schema for this dataset. I initially thought that it would be simple to determine which table should contain which data and which columns should ...
0
votes
1answer
12 views

Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt?

The current analytics solution that my team works with cannot handle changes in the structure of one of its hierarchies. If a new level or branch is added, then the entire domain must be first backed ...
0
votes
0answers
22 views

Need help on designing database schema for high frequency update app

This app will call API endpoints every second and get JSON data and it needs to be saved in DB and then query it to the client. But Before saving, it needs to check if the data already exists or not, ...
1
vote
1answer
22 views

Help defining a chained one-to-many relationship tables

Sorry if the title is confusing but I couldn't think of a way to properly word this question without laying out what I am trying to do. Basically I have 3 tables and I'm trying to figure out how to ...
0
votes
0answers
16 views

How would I model a database that selects from a group of tables? [duplicate]

this is the first time I have ever tried to design my own database, so I'm sorry if this is a bit of a stupid question. I have been trying to figure out how to create a database to sort an inventory ...
0
votes
1answer
29 views

Schema and Query design for table with multiple languages [closed]

I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and ...
0
votes
1answer
31 views

What is the advantage of setting a SSN column as VARCHAR(32) over VARCHAR(255)

What is the advantage of setting SSN column VARCHAR(32) over VARCHAR(255) even though the two of them consume 2-byte memory?
0
votes
1answer
35 views

Groups and Members database: Which of these two options is a good design? [closed]

Each group can have either 1 or 2 members, no more, no less. Every select of group data will need to retrieve the member data. The eventual state of each group will tend towards 2 members (full group)....
0
votes
0answers
43 views

Is a Booking System where User and Participant are Different Entities Correct?

Background I am building a event booking system for a youth organisation. The events that can be booked are targeted in general at children under the age of 18 (i.e. which in Europe means that you ...
0
votes
0answers
22 views

Storing objects in key/value stores - serialized or separate values?

I'm playing around with Go and BadgerDB, which is an embedded disk based (not in-memory like Redis) key/value store. There's just so little information about how to use key/values stores in general. ...
0
votes
1answer
17 views

Should I include another column here to keep track of cost + tax or not?

I am making a two database tables tho add a sales order line item functionality to my application. On the second table, which lets call salesorderlineitems, I have the following schema CREATE TABLE ...
0
votes
2answers
20 views

Database layout to keep track of the “freshness” of a column value in a specific row

I have a Person with columns like name, address and bio. How do I store data in order to know the "freshness" of these columns? For instance, "for the Person Y, the name has changed 2 days ago, and ...
-1
votes
0answers
28 views

Are Database Systems like Booking.com's or the one of most airlines at least in 3NF form? [closed]

Background I am designing a new system to book tickets to one of our events. The requirement here is that the event is targeted at children, but the tickets are usually booked by their legal guardian....
1
vote
1answer
29 views

Duplicate column or foreign key lookup?

I have a base user profile table that stores data for users. Each user is able to upload multiple pictures so this is represented by a separate photo table. When the user profile is queried, the ...
0
votes
0answers
19 views

Designing a generic associative table

I need the ability to associate two entities of the same or different types. Take the concept of planes and trucks that have the ability to radio to each other. +----------------------+ | ...
-2
votes
1answer
26 views

Requesting review of proposed database for Project Billing [closed]

I've had enough experience with databases to get me this far, but I was hoping someone could validate my design. Once I have this confirmed, next steps would be to implement in some tool, and use ...
0
votes
0answers
34 views

Is My Normalization Exercise Correct? [closed]

Considering the following case: I came up with the following designs: 0NF: Order [OrderNo, OrderDate, (CustomerNo, CustomerName, CustomerAddress, CustomerShippingAddress, CustomerPhone, ...
0
votes
0answers
12 views

MONGODB -> Whats the best DB model for friendship user Relationship

Good morning: I want to implement friendship between two users, that friendship is defined by a status (accepted, pending, blocked). It only occurs to me to use a collection as follows: USER1 - USER2 ...
4
votes
2answers
75 views

A table with all fields NULLable with important, sensitive data

I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc). On screen the information is sectioned into tabs - "alcohol",...
1
vote
1answer
40 views

What do the entity-relationship diagram (ERD) symbols used in DBeaver mean?

I don't understand the ERDs used by DBeaver. The diamonds and dots at the ends of the line arent the crow's foot I'm used to. I've googled the subject and checked the docs but can't find a straight-...
0
votes
1answer
27 views

Best way to put date range in a table in mySQL

I'm designing a system which is basically a booking engine. it uses separate table to store room data and separate table to store it's availability information (available date(s) ). I'm stuck in ...
0
votes
0answers
25 views

Database design for a messaging system involving group and private chats

I'm designing a messaging system where users are able to start 1-1 chats with each other as well as participate in group chats. I'm deciding between: Normalizing the underlying chat structure (basic ...
0
votes
0answers
36 views

Database table design for product with multiple options and various options offer more options

im having a hard time coming up with a database design for products that have multiple options however in some cases one option may even lead to a new option to appear. Say i have Backpack as a ...
0
votes
1answer
38 views

Modeling a graph of different node types and edge types using SQL Graph

I am looking into using SQL Server 2017's SQL Graph feature to model a graph that has different node types and edge types, but am confused as to how I would model this. In short, a user can create a ...
2
votes
1answer
45 views

Database Design for a Graph (Network Topology)

Is there a recommended pattern for modeling a graph in a relational database? My presumption is that you'd have the following three tables: Graphs ------ GraphID, PK GraphName Each record in the ...
1
vote
0answers
24 views

Centralizing Databases

I have a very challenging scenario at hand and would like to hear suggestions on the solution I am considering implementing. I have several databases that have similar (non-identical) structures and ...
0
votes
0answers
12 views

Is Data Warehousing the Right Solution [migrated]

I have a vendor system that I can query to get reports or raw data. The data returned are based on internal calculations, I can get also get data based on dates. I have some reports that tend to ...
6
votes
2answers
138 views

New boolean column in big table OR - bad idea ? - new related 0-1 table

I have a big table (200 billion rows) like this : CREATE TABLE A ( A_Id INT NOT NULL PRIMARY KEY, A_Attribute1 VARCHAR(100) NOT NULL, ... ) I must add new boolean column NOT NULL with false as a ...
1
vote
1answer
65 views

Hotel Booking Database design problem

I am currently designing (as an assignment for the course at the university) a database for online booking of hotels worldwide and I have stumbled into a problem. Here's is the conceptual design: ...
-1
votes
1answer
28 views

What is the best DB schema to handle exceptions and prevent redundancy?

There are some products that can be sold in multiple units and the base unit is kilogram. The unit of each product has different weight, for example a Bundle of product A is 20kg but a Bundle of ...
1
vote
2answers
36 views

Extra rows or Quantity Column DB Design [closed]

Me and a coworker are seeming to disagree on this topic. The context is Product that have multiple components. Sometimes its multiple of the same component. I think its better to have a record for ...
0
votes
0answers
29 views

Double Entry accounting - table design and showing a transaction list

There have been a few debates on table design for an accounts system. I'm trying to make a little home finance app to learn some new technologies. My requirements though, are that a transaction, as ...
1
vote
2answers
39 views

How to design database for market place website [closed]

I do not have much experience in table design. My goal is to create a multi-vendor market place website with laravel that meet the requirement below: Support many kinds of products (10 producs) such ...
1
vote
1answer
38 views

Can and should relational database contain tables that don't have primary keys? [duplicate]

So this site says: "All tables in a relational database should have a primary key". Here it also says that every table should have a primary key. But if I look here and here they say that tables ...
1
vote
1answer
30 views

How to query for address “predictions” while applying constraints

Let's say I have this simplified database scheme. The scheme consists of three tables, street, city and zipcode that all have a many-to-many relationship to each other. What I want to do is a ...
-1
votes
2answers
40 views

EAV in postgresql

I'm building an application that needs to be deployed several times, with a relatively well organized data model. A table needs to be configured for each deployment with a small number of additional ...
0
votes
2answers
26 views

Database design question: Tags with values(duration) linked to them

I am creating a database that takes in daily inputs such as: Date Events in that date (e.g. project foo: 5 hours, bar: 2 hours, etc...) Rating of the date There's more but these are probably the ...
0
votes
3answers
42 views

Should I create a new table or save “static” data in all the tables? [closed]

I'm using Postgres as the database management system. Currently, I have a countries table, which has only four countries, i.e. four rows (might add one or two more countries, i.e. rows, later). --...
-3
votes
1answer
29 views

Which database to use? Storing one string every second for several days [closed]

I would like to observe some cryptocurrency prices and for that, I want to store a string, everey second in a database for ca. 3 days. The string will vary in length, it will be 2500 characters max. (...
0
votes
1answer
26 views

Help with an entity-relationship diagram re Manager entity type and its relationships

In the below ER diagram: A manager can have many Users but a User can only have 1 Manager A Manager must be able to assign multiple users to multiple courses Are the manager relationships correct?
0
votes
0answers
44 views

How to store a risk-like game map [migrated]

Imagine a multiplayer online game like Risk where there is a map represented by a NxN grid. Each cell can be free or occupied by a player. When a player access the page, the grid is displayed. ...
2
votes
4answers
78 views

Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, …?

We have items of three types to be stored and queried according to some business logic. create table a_table ( item_a varchar2(30), item_b varchar2(16), item_c varchar2(2), -- other ...
0
votes
0answers
29 views

One table to hold all ids related to one user - bad idea?

I was wandering if it's a very bad idea to have one table that holds all the ids related to one entity? For example let's say i have 3 tables each containing data about one user. Should i use an ...
0
votes
1answer
44 views

Storing dates and their history of changes and emailing - Does my database design pose any problems?

Background I'm designing some tables to allow dates to be assigned to an object so progress can be tracked on that object, along with functionality to store emails sent pertaining to these contracts ...
0
votes
3answers
84 views

What is the standard way to make a invoice system database tables?

I'm confused how to create tables for invoicing system, because when one invoice have more products, I can't repeat same invoice id for the each product in the invoice table as PK cannot repeat. so ...