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
1
vote
1answer
18 views

Company supplied data list, with customer additions

Imagine a company/supplier supplied table of items table - blueprints id [pk] name color blueprints - 1 - chair - pink - 2 - table - blue As a company we would supply blueprints as a global list, ...
0
votes
0answers
14 views

How to design MySql database design for Jira-like application?

I am new to software development and developing one application for learning new skills, I am trying to implement some of the basic features of Jira software. I am stuck at database design, I have ...
-5
votes
0answers
28 views

convert sql query into laravel (urgently) [closed]

function show($status,$main_table,$table) { $status=''; $check=''; $current_date=date('Y-m-d'); $provision=''; if($main_table=='companies') ...
0
votes
0answers
10 views

Schema to generalise potentially parametrised status

Let’s say I have some entity: create table entities ( id serial primary key, foo text ); ...and I want to assign it some state, which has N possible values. Some of said states also come with a ...
0
votes
0answers
14 views

Design read and write user permissions on an entity

I would like to enforce user permission to read and write folder and file entities. By read I mean SELECT and by write I mean INSERT or UPDATE. I'm not sure how to model this,and would like to learn ...
-1
votes
1answer
31 views

Does Postgres store many columns with mostly NULL values efficiently?

Let's suppose I have a table with 500 columns (or even just 100, to be more realistic), and most records only use the first and last column, the other being null, will PG store this as: "John Doe&...
0
votes
0answers
39 views

Large number of connections to an SQL server results in read write bottleneck on hard drive?

I have the following problem which I can't find a solution for. We have a learning School System for more than 10000 students that upload photos and PDF of Homeworks as binary to an SQL server. During ...
2
votes
1answer
46 views

Database schema design implementation feedback

I'm having difficulty on designing a database schema design with the right relationships. I'm currently trying to design a database system where a question can be answer via a live_stream. The ...
0
votes
0answers
7 views

System for managing matches and teams

I would like to create a system for managing matches and teams in an amateur championship. I had thought about this type of relationship. By default ONEtoONE has been set. Can anyone tell me if the ...
0
votes
0answers
34 views

modelling bill split app schema

I've been trying to model an efficient schema for a bill split app, the requirements are as follows: There can be groups in which there can be multiple participants, and conversations consisting of ...
-2
votes
1answer
28 views

Would this kind of table structure be wasteful and stupid, or superior to my current way of doing it?

I currently have this table structure: CREATE TABLE people ( id bigserial, timestamp timestamptz DEFAULT now() NOT NULL, PRIMARY KEY (id) ); ...
0
votes
2answers
13 views

Queue like behavior with read once

I’m looking for a data store to back an API, and since I know of no data store that handles it’s data they way I’d like, I’m thinking good ol’ PostgreSQL might be suitable for now. Only problem is ...
2
votes
1answer
39 views

Should I use null or a separate table?

I could have a nullable field or I could do basic normalisation, having another table which may or may not join to a row in this table. My instinct was to use the second method, as this is the "...
2
votes
1answer
44 views

MySQL DB design for very long growing row size

My data has the property to grow in the row direction and the column direction at the same time. The size will grow in both directions without limit while I do more research. The number of columns ...
0
votes
1answer
32 views

Multiple values for a single column

I've got a small database with 5 tables: 3 of them are resources (material, skill and tool) and the other 2 are tasks (corrective maintenance and preventive maintenance). Every table has a primary key ...
0
votes
1answer
32 views

Database design - Projects assessments

I am designing a web application based on the evaluation of projects, each data in each column must be evaluated in assessment_criterias, How can I make the relationship in the field in the assessment ...
5
votes
2answers
51 views

Foreign key part of a composite primary key

I have a table Room with primkey on column room_id and another table Items with primkey item_id. Each Item has a foreign integer key to room_id. When my program runs, it only looks at items in a ...
-3
votes
0answers
22 views

How should huge yearly data be managed?

I mean for example do banks with lots of yearly transactions, keep all data in same table or lets say yearly handle a maintenance and transfer old data to some archive database or something.
0
votes
1answer
23 views

Query specific key fields from x-number of tables?

I'm building an insurance database and I need some help with best practice on fast quering data. Let's say I have three insurance types ... Car, Home and Travel. Common for all three types, I have the ...
0
votes
0answers
46 views

Database design and relationships between users and tables

I am trying to design a database and i am having some questions, hopefully you can guide me for the next steps. Setup My stack consists of Node.js in the backend and my test DB is in Postgresql but i ...
0
votes
0answers
24 views

Text type vs CIDR?

I have a table in which I store IP prefixes. I know that Postgresql offers the CIDR type to store Network addresses however I found out that in my case using this type as a primary key and foreign key ...
1
vote
0answers
34 views

Assistance to understand the Microsoft's Inventory Database model

checking the Database Model of the Northwind Database I could not understand the relationships of the [Inventory Transactions]. Mainly this table will store In and out transactions based on [Purchase ...
2
votes
1answer
31 views

How to deal with data repetition?

I am unsure if this has been asked already. Tried searching but couldn't find one. I'll explain with a close example (I call it the tags problem). Consider a basic CMS. A post can have many tags and ...
0
votes
1answer
30 views

Series of questions about ER diagram and database? [closed]

I have newly learned about ER diagrams and how powerful they are. I am trying to make a very basic ER diagram for hospital and there are few questions and doubts in my mind. This is the ER diagram ...
0
votes
0answers
38 views

Solutions to RLS (Row Level Security) in PostgreSQL for Desktop Application?

The Situation: I have a desktop application that currently uses a database which is stored locally on the computer that is running the application, and I am starting to implement a server DB so that ...
0
votes
1answer
30 views

Common entities with differing attributes across multiple databases

We have two systems each with their own database. Each system has Parties (Customers and Vendors) and each of these can have one or more Sites (addresses) for different functions (delivery, holding, .....
1
vote
1answer
27 views

What is the preferred / common way to separate “access-tokens” in a database design

I'm currently having a case where a user has to verify their email, is able to reset his password and can log into a web-interface (so far and not limited to). There a now 3 places where some kind of ...
0
votes
1answer
35 views

Mysql : what are the steps for creating a database schema for a big project with many tables?

I am creating a project with lots of tables like one for taxes, one for employees, customers etc. So how should I go about it? For eg: building the ER model first or something like that
-3
votes
1answer
23 views

Is file system in DBMS same as file handling in C programming language?

We know that file system in not good for storing data , thus relations(tables) are used in DBMS. When we talk about files in database management system (DBMS), are these same/similar to that in C ...
0
votes
0answers
21 views

PostgreSQL : change the type of a domain

I have a PostgreSQL DOMAIN "mydomain" based on NUMERIC(8,2). I need to change it to be based on NUMERIC (12, 2). That DOMAIN is used by about 50 tables. ALTER DOMAIN doesn't allow to modify ...
1
vote
1answer
62 views

Database software solution [closed]

Our company has user data (name, email, phone number, systems of access, AD username) in a large Excel workbook. The Excel workbook is becoming very slow because of the 1000s of entries in it, and we'...
1
vote
1answer
24 views

Should I capture all the relationships in a Database design?

I am a newbie when it comes to database design. I was trying to create a blogging application and I ran into a problem. My database consists of the following tables: User HasInt (Records which user ...
0
votes
1answer
30 views

is it possible to populate a primary key of a table based on a column of another table?

I have a table that contains a text-type column(for example graph path : 12 5 7 19 30 ..) and I want to split this text by space and put all of the output strings as a primary key in the other table. ...
0
votes
0answers
11 views

How to design database with post sharing with public, specific users or hide from specific users

I am new to database design, so please ignore if i am asking very simple, but i have searched a lot about this but didn't found some helping material. So, the question is, I want to design a database ...
0
votes
0answers
17 views

Relational DB which can do inserts at billions of rows with multiple indexed columns?

I am looking for advice on a database to potentially replace SQLite for my application. To briefly describe my DB requirements: needs to support billions of rows and the most queried table has ...
0
votes
0answers
19 views

Table as an Index?

PostgreSQL 11 Requirements: Uniqueness must be guaranteed (not duplicated) MUST not lose any data. The data I get is NOT uniquely identified, so I am left with semantic comparison of the values to ...
0
votes
0answers
17 views

How can I connect central and decentral databases?

I am working in computational science and we are frequently producing large amounts of highly-structured data that depends on various input parameters. Right now, storing the data is managed via files,...
1
vote
1answer
48 views

How to represent foreign key in an Extended ER Model in Chen notation?

This is a spin-off of the question at How to represent foreign key in an ER diagram? which is too vague about the needed notation. There the idea of underlining FKs with a dotted line is stated. I ...
3
votes
3answers
75 views

Postgres foreign key to a foreign key [closed]

Apologies for asking this like an interview question... Say you have 3 tables A, B and C A is huge B references A’s primary key through a foreign key and is small C wants to reference the data in B ...
1
vote
1answer
44 views

Is this correct normalization for my tables?

I think I'm confusing myself and making this too complicated. At this point, I need outside advice. I have a Laravel 7 application where a user will enter orders. Each order has one customer. Each ...
-1
votes
1answer
31 views

Where does data sit and where must it be encrypted

what is does it mean when data-at-rest is not encrypted at the datacenter. Does this mean that the data sitting in the database is not encrypted? I am just trying to understand if data sits on the ...
0
votes
0answers
20 views

Designing associations where no obvious relationship

I hope this question is in the appropriate place. I'm trying to work out the best way to design associations between two tables with no common id's at present. I have them referencing table names and ...
-1
votes
1answer
26 views

Advice on design [closed]

I am new to databases and overall data designs. I am developing an English learning website that keeps progress of your studies. My problem: Every lesson has a checkbox which marks your progress. ...
-1
votes
1answer
24 views

What's the point of primary dense indexing?

I'm refreshing my basic knowledge of indexing specifically to database indexing. Pratically, I don't put much attention to how it works internally I just do that CREATE INDEX index_name and assume ...
0
votes
0answers
18 views

How to prevent a circular citation?

This is a follow up query of this I am using : mysql Ver 15.1 Distrib 10.1.44-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT, title ...
0
votes
1answer
16 views

How to add an additional table over two one to many relations tables?

I have two tables TableA and TableB which have a relation of one to many Later I am told that there must be an additional table that links the initial tables with additional information. To carry ...
-1
votes
1answer
26 views

How should cardinality be understood in a table with a compound key?

I am learning about database structuring and when watching the following video I cannot identify if there is a cardinality between the ENVIO, PEDIDO and PRODUCTO tables? If it is clear to me that ...
0
votes
0answers
22 views

Clarification on SQL Server database replication / Distribution / Performance

I started reading about the scaling database lately due to my recent engagement, and so far, I find it interesting and have been able to successfully set up transactional database replication with SQL ...
0
votes
1answer
21 views

How do I make a citation table where work can't refer to itself?

CREATE TABLE Work(id INT NOT NULL AUTO_INCREMENT, title CHAR(20), date DATETIME, description VARCHAR(1000), CONSTRAINT Work_PK PRIMARY KEY (id), ) ENGINE=InnoDB DEFAULT CHARSET=...
2
votes
3answers
33 views

The right way to model multiple FACTs schema

Background I'm in a process of designing a database (using a STAR schema). There are three tables to model: products, tests, states. The database will be used to store results of tests conducted on ...

1
2 3 4 5
107