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
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
27 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?
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 ...
0
votes
0answers
13 views

questionnaire selection model

I'm currently working on modeling a database to "recommend" products based on a questionnaire (it's not a recommendation system) so I have my products with some characteristics like the ones bellow (...
1
vote
0answers
25 views

Aggregation vs Ternary relationship - When to use?

I am reading a book about database systems and in the chapter covering database design using ER, the author aims to explain when to use aggregation versus ternary relationship. In short, each Project ...
1
vote
0answers
46 views

Different “Master” Tables and Same “Child” Tables

I am working on Student Life Cycle management system which is in the design stage. Right now I am creating the database structure. My requirement is as follows: Every year a student faces three term ...
1
vote
0answers
22 views

Unconnected tables for predefined options in MySQL DB [closed]

Is it okay to live some tables not connected to anything? I have a table with a set of discounts for deals. For each deal, discounts can be summed up, so I don't see the point in connecting the "...
0
votes
0answers
10 views

DynamoDB: How to model data that's shared and queried by all users?

I am very intrigued by DynamoDB, and it works incredibly well when I model the data for my main use case for my application. That being said, there is one specific use case that I can't wrap my head ...
0
votes
3answers
152 views

Replace column by LEFT OUTER join to save space

I have a table t1 with 100M row. For column A, 95% of the rows have value NULL0, the other records have values from 1 to 9. To shrink my table, I'm thinking about moving column A to table t2. t2 ...
0
votes
1answer
34 views

Database design for Scavenger Hunt Game

I'm designing a section of my database which links User, Team and Hunt. I would like users to be members of a team but the team can play multiple hunts (so can be used multiple times). The first idea ...
0
votes
1answer
20 views

Strategies for Database Schema Redesign on Un-Normalized Group of Order Tables in Inventory System

Being my first database-driven and SQL powered program, I'd created this more or less on-the-fly, learning as I go.. trying to be cautious- but more or less created with the intent of simply working.....
2
votes
1answer
31 views

Should log tables be “normalized” or store snapshots?

Say we have the following structure: USER --- USERNAME CONTACT --- ID FIRSTPHONE[FK TO PHONE.ID] SECONDPHONE[FK TO PHONE.ID] PHONE --- ID NUMBER ...Now, when creating an log table logging when a ...
0
votes
0answers
27 views

Storing earth observation derived attributes for polygons

I want to generate a number of vegetation related indices (NDVI, NDTI, etc., min/max/avg/std (pixel values)) from earth observation data for a large number of polygons during the entire growing season,...
0
votes
1answer
34 views

What is the proper way to relate many to many?

I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
0
votes
0answers
18 views

General pattern to dedup records

I'm storing a large amount of time series data/value (varchar) with timestamp for a set of parameters (varchar) in PostgreSQL. I'm looking for a way to avoid storing duplicate if the value is the ...
0
votes
2answers
29 views

Best Way To Create Time Series from Postgres Table Update History? [closed]

I have a Postgres Table (500 rows, 10 columns) that gets updated every 6 hours by a Daemon (that performs some calculations) and executes a series of row-by-row upsert operations. How can I construct ...
4
votes
1answer
84 views

Complex Delete Cascade Including Self-Referencing Table

Above is a diagram of my data structure. It represents a hierarchy which can contain three different types of "elements": "A"s, "B"s and "C"s. The relationships show the delete cascade behavior I ...
1
vote
1answer
33 views

What is the meaning of “dimension key”?

I am a beginner trying to understand the basic concepts of database's. I know several types of keys (Primary key, Foreign key, candidate key, super key, surrogate key.....etc). While learning about ...
-1
votes
1answer
32 views

what is the difference between 'order' and 'order line'?

I was trying to understand degenerate dimensions. But I quite often encounters the term "order line" as grain in an transaction table. Could someone please explain the difference between the order and ...
0
votes
0answers
23 views

Database design for multiple ordered component types on a page

I am creating a page type in the database that would represent a page on a website. The problem is that each page could have multiple components such as: text, image, video, audio file, etc. I am ...
1
vote
0answers
26 views

How to model complex relationship between three Models in Django

I have to model the following entities: BuildingSite, Company, Worker. Each entity has a many-to-many relationship with the other entities, and each relationship some attributes like start_date, ...
1
vote
0answers
47 views

Improving Query Speed By “Normalizing” [closed]

I am creating a dynamic report on some data. This is using a reporting database, and I am only concerned about query speed, not insert speed, or anything else. My first plan was to start extracting ...
1
vote
0answers
55 views

Crow's foot notation--what is the difference between “one” and “one-and-only-one” regarding relationship cardinalities?

I'm trying to figure out the difference between the single-bar and double-bar cardinality indicators as in the figure below: Some sources seem to use one notation while others use the other. I can't ...
1
vote
1answer
34 views

Is there any advantage to putting some columns in another table, or should I just put them in the same table?

I have a SQLite database with about 50 tables in it. The largest table contains location data and it has 6,000,000 rows. An example of some of the columns in this table are: location_contrivedkey --(...
0
votes
0answers
24 views

Revenue Forecast Database Design

I am currently trying to design an Access database that will house financial data containing forecasting, actuals and prior forecast data. The current format of the Excel "database" is Customer ...
2
votes
1answer
47 views

Hierarchical references abstraction with strict model

I want to abstract a real world hierarchy in a strict DB model (as strict as possible). My problem is to to create a structure with a this-or-this-but-not-both rules set. I have two entity types, ...
2
votes
1answer
56 views

Should I normalize a relational database design if I know the data will never be updated?

I'm designing a PostgreSQL database that will be storing an archive of data that I am absolutely certain will never be updated, only read (these are archives will be pointing to real data at a point ...
2
votes
1answer
31 views

What is the best way to design one-to-many relationship with null value?

What is the best way to design one-to-many relationship with nullable FK? I have two tables: TableA: Id PK Name TableB: Id PK TableARef FK Name TableB has reference to TableA but this ...
0
votes
0answers
16 views

Need help on table design how to consider when an item from table_a is referenced on table_b

i'm out of idea. i first want to inform everyone that i am in no way a DBA. assuming i have table design as below: table_a: -------------------------------------------------------- - id - ref_no - ...
1
vote
0answers
27 views

How to model entity types that can contain each other? [duplicate]

I'm having a hard time figuring out how to model a database with a Many-to-Many relationship between a concept of Markets & regions of the world. The issue I'm running into is that a Market is ...
0
votes
0answers
36 views

Common way to handle a changing reference in dependency of status

I am new to SQL an SQLite, so this is probably a silly question. I am designing the data model for a commercial application. While documents (like quotes) are being created, I need to reference to ...
3
votes
1answer
68 views

Need help in picking solution and understanding when to add columns vs EAV

I've been working on the following problem for a long time and I can't decide between two solutions. While both would work to solve the problem, neither "feels" exactly right to me. Any advice on ...
0
votes
0answers
26 views

What are the pros & cons for normalization for a database in which user input can expand the fields being measured?

First and foremost, a disclaimer: I've attempted to search for duplicates that answer my question, but I did not find any. Sorry if they exist. Also, I know that questions here are supposed to result ...
0
votes
0answers
23 views

Entity Relationship to One of Several Other Entities

I'm building a database model that requires one table to have a relationship to only one of several other tables. For example, a Contract may be associated with a Person, a Business, or a Government, ...
0
votes
1answer
36 views

database design - psychometric testing layout

Good Afternoon, I'm working on the table layout for a SQL database that will store individual and test information. This includes individual identifying information like Name, medical id, date of ...
0
votes
0answers
49 views

Modelling Orders and Messages for an e-Commerce database

In my e-commerce application I would like that buyers and sellers interact via a messaging system to purchase products. The process consists of: The buyer inquiring for a product The seller responds ...
1
vote
1answer
54 views

Constraint on multiple columns?

I have a table edges, which describes relations in a graph: CREATE TABLE IF NOT EXISTS edges ( src INT NOT NULL REFERENCES nodes(id) ON UPDATE CASCADE ON DELETE CASCADE ,tgt INT NOT NULL ...
0
votes
0answers
12 views

Flash card app, more efficient way for my tables?

To practice my skills I'm building a flash card app. As I have no experience in designing a future proof database I'd love to hear your feedback. For the initial version I just want to have following ...
0
votes
1answer
39 views

Design critique on a table to keep track of which person flipped a switch at what time

I created a table to keep track of which person flipped a switch at what time. It consists of: create table records ( name varchar not null , changed_at timestamp with time zone not null , ...
1
vote
1answer
55 views

Purging of unreferenced data via unconditional DELETE, by leaning on foreign key constraints

I'm designing a database from which old data has to be purged regularly for legal reasons, and I'm trying to figure out the best way to organise the purging. Dealing with the main tables is trivial, ...
0
votes
1answer
35 views

How to link customers and suppliers in a database

Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier ...
1
vote
0answers
47 views

Is a relation with two attributes always in Boyce-Codd Normal Form (BCNF)?

I went through some tutorials on BCNF and got that a relation with two attributes is always in BCNF. My question What if a relation with two attributes has no prime or non-prime attributes? That is,...
1
vote
2answers
61 views

Deduplication/normalization of string column on insert

I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text ...