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
0answers
16 views

Efficiently finding the ultimate parent in multi-level one-to-many tables

Let's say I have a database with multi-level one-to-many tables (a contrived example): CREATE TABLE users ( id SERIAL PRIMARY KEY ); CREATE TABLE properties ( id SERIAL PRIMARY KEY, user_id ...
0
votes
0answers
5 views

Simple database to link subjects and exam boards

I'm new to Sql and want to use sqlite to create a database to store data about which topics are needed by which exam boards. So far I've created two tables - one called board and one called topic. I'...
0
votes
0answers
10 views

User ID for each entity in database

Is it ok to have user ID for each entity in Postgres database? i.e Customer can have a customer id and as well as user id, staff can have employee id and also a user id
0
votes
0answers
12 views

Create a messages table that has restrictions on who can send messages

I'm creating a table for a web application, the web application has a messaging feature. The web application has normal users (let's call them visitors) and companies. Essentially, the companies are ...
0
votes
0answers
11 views

Cassandra data modelling for IOT devices

We are building a micro service based IOT platform with a dashboard and views of various sensors etc. I have seen a few data modelling videos but I am not able to arrive at a good data model in ...
0
votes
1answer
29 views

How to store a bunch of columns and where you don't know how many there will be, the columns change, and new columns are introduced

So the so source of the data is the internet, and random bots that submit values to my apache server. Periodically new columns appear, but they are not always present. The other problem is then you ...
-2
votes
0answers
14 views

help me in database normalization and ERD [on hold]

pleas can any one help me in my database assignment i have problem with the normalization and Entity relationship diagram
-1
votes
1answer
23 views

how many concurrent session does oracle database support

I want to have an application linked to oracle database the users will request data through the app and the app will access the database and retrieve the info. my question is how many concurrent ...
0
votes
2answers
40 views

Is multiple dbs is bad? [on hold]

i am a junior programmer who have no background or so ever in dbs, i only encounter mongodb during my yearly programmer work and these it. Lastly I'm trying to understand the advantages of micro ...
2
votes
1answer
42 views

Using a second table for unique key?

This is a fairly noob question. I work with an old-school PowerBuilder software (PowerCampus) that has some strange design decisions from the 80s and 90s. What's perhaps the most important table in ...
2
votes
2answers
126 views

Which DB for 1 table with 100 Million rows per year?

I've a questionnaire with 70 questions and need to store the answers. Problem: it's 100 Million records per year. I've experience with different types of storages, but never had to deal with these ...
1
vote
1answer
36 views

DB design advice - shortcut foreignkeys vs long queries

I've designed quite a few databases but there's a problem I come across time and time again. The more information you want to pack into your database and the more fields and different properties you ...
0
votes
1answer
25 views

What is Query Containment? [on hold]

Any one knows what is query containment and where do we use it please? As far as I can search on google, I only found one here. Although, I've got some idea on the subject but i'm finding it ...
1
vote
1answer
36 views

Table design - parent with always exactly 1 or 2 children

Lets say I have the following existing tables (both with a primary key column named Id): TableA TableB Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 ...
0
votes
1answer
65 views

Is there a database table design to avoid storing table names in a table?

So my scenario demands to store details of businesses which offer varied services. One business can offer n different services. Since each service is different, details pertaining to a service are in ...
0
votes
0answers
23 views

Can a decomposition of a database table into 2nf, or 3nf yeild different table results depending on the sequence of splitting?

I am not able to check if the decomposition of a table into 3nf, or 2nf form will depend on the sequence of decomposition. Please help, I want to be assured that even if I adapt different sequence of ...
0
votes
2answers
40 views

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

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
1answer
26 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
32 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
26 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
28 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
47 views

Assistance with appropriate schema for this dataset involving the services of parcel couriers [on hold]

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
13 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
24 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
23 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
32 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
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
21 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
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
47 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
27 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
37 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
41 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
46 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
25 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
13 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
139 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
69 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
37 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 ...