The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [database-design]

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

925 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
0answers
22 views

Schema design: Anonymous user's data across registration

This is a schema design question; implementation is going to be Mongo, but answers are platform agnostic. Given an app, which has users, sessions, and items. All visitors will have a session ...
1
vote
1answer
45 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 ...
1
vote
0answers
33 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 ...
1
vote
0answers
26 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 ...
1
vote
0answers
123 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
48 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
28 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
142 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
0answers
65 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
1answer
41 views

Random selection with a “complex” subset structure

This is my first question in the whole StackExchange world. I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question. In my ...
1
vote
0answers
27 views

How to implement correctly from SQL to NoSQL (DynamoDB)?

I am just new in NoSQL and DynamoDB. I try to moving from using AWS RDS to DynamoDB. I have table user which can have more than one of work experiences and can have more than one of interest (table ...
1
vote
1answer
131 views

Which table gets the FK? Is there a clear Parent-Child relationship here?

I have the following tables: Each ProductLine will encompass many Parts. For this reason, it initially seemed to me that the Part table is a child of the ProductLine table. However, a part is not ...
1
vote
0answers
50 views

UNIQUE constraint encompassing all the columns of a table - is it bad practice?

Is it considered bad practice to have a UNIQUE constraint applying to all columns on a table, essentially enforcing unique rows? My use case is to store tokens from textual message from various ...
1
vote
0answers
22 views

Database design for roles for members of organisations

I'm developing an IDP providing the same REST-api for a number of organisations. Each user is a member of at least one organisation and it is based on OAuth2 and OpenID Connect. Every user of every ...
1
vote
0answers
93 views

Storing GTIN/EAN values with PostgreSQL. Which data type to use?

I am building a product database and I need to store GTIN/EAN (Global Trade Item Number/European Article Number) values. Probably except GTIN-14 as it does not seem to be used at retail sales? I read ...
1
vote
0answers
21 views

Referential integrity based on data more than a table away

Notes on tables A, B, C, D, E B.aid and C.aid reference A.aid. D.cid references C.cid and D.did is the primary key. D is a C. cid and did are pre-assigned and I cannot change them (they need to ...
1
vote
0answers
29 views

Design for tables with the same column value but different names

I have the following three tables: doctors (Doc_id, Doc_name) patient (patient_id, patient_name) admission (patient_id, sup_Doc, date) What is the correct way to go about linking the doctor and ...
1
vote
2answers
124 views

How to represent the relationship between artists and albums in an entity-relationship diagram

I'm learning and practicing with the E-R model trying to design a music industry database. With this section of the diagram I have a doubt: I suppose an album can be had by an artist and a band, not ...
1
vote
1answer
58 views

Database structure for a large amount of data with different types

Can I ask you for give me tips how to design efficient database to store large amounts of data with different types. I would like to use SQL Server 2016 as the database management system. I have to ...
1
vote
0answers
51 views

Designing a table with precalculated team values for fast read queries

My version of PostgreSQL is 10.7. As it's something I can't calculate on the fly due to the amount of times it might be used, I decided to store all the 11 million combinations and their result on a ...
1
vote
1answer
43 views

One user affects all other users

I'm completely new to working with databases (other than retrieving data from them as a front end developer.) and have an idea I was toying with. However, I'm struggling to find exactly what to Google ...
1
vote
3answers
46 views

How to PARTITION a table in mysql?

I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of CREATE TABLE pages ( page_id int(11) ...
1
vote
2answers
28 views

Cursor-SQL Help- Need another Column for Dynamic SQL

My company decided to change the value "Unknown" to (-1) in ALL of our lookup tables (tables that end with 'LU'). I am trying to write a script to automatically add a value for VALUES(-1, 'Unknown') ...
1
vote
1answer
66 views

Normalization: Should I do this in 2NF or 3NF?

Consider a database storing the data of invoices, and each invoice takes the following form: Invoice_Num: 1480 Handled By: John Keaton Staff Num: 263 Staff Position: Trainee Item_Num Item_Desc ...
1
vote
0answers
94 views

Worked example of closure tables with from and thru dates

I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now. ...
1
vote
0answers
49 views

Modelling a database for a shopping list - should I represent the scenario with a “redundant” relationship?

[I may provide some pictures later] I'm facing this issue in modeling an entity-relationship diagram. I'll have a shopping list, where for each item the user inserts, it'll be associated with one ...
1
vote
0answers
19 views

Design table that solve a service payment or a package service payment?

I am learning and practicing some database designing and case study on Selling Services on Internet. In my requirement, user can buy a post service and add more extra option if they need. In fact, ...
1
vote
0answers
121 views

Database design for multiple units of meassurement and pricings for each item/product

Table Products id_product| name | .... 1 | abcd | .... --- Table Pricing id_price | qty | price_type | price 1 | 20 | discount | 20 --- Table Unit id_unit | ...
1
vote
1answer
31 views

When creating a many to many relation with an attribute, should I include a non-composite primary key?

For example, I have frame and voltage tables, both of which relate to a motor object: frame(id, motor_attribute); voltage(id, voltage_attribute); I can pick a frame and pick a voltage and for each ...
1
vote
2answers
46 views

Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

I am facing the following problem: I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country ...
1
vote
0answers
25 views

Db Design: Junction table or column

I have three tables like below. user company user_company -------- ------- ------------------ id(pk) id(pk) user_id(pk, fk) company_id(pk, fk) My ...
1
vote
0answers
48 views

Cardinality with specialization in enhanced entity-relationship diagrams

I'm currently taking an introduction course to databases and I've come across a problem not described in the textbook. I'm wondering about the use of cardinality with the use of specialization in ...
1
vote
0answers
17 views

Best practise for data dump 'entry point' in DB processing chain - PostgreSQL

I need to implement a process chain to transform and analyze potentially large sets of data, inserted both programmatically and manually (e.g. CSVs). In either way, the input data is standardized in ...
1
vote
1answer
30 views

redesign database around selecting rows by time range

I currently have a MySQL database that grows about 50,000 rows a day. When querying data out of the database, I query information from the info table and use a subquery on the session table to refine ...
1
vote
1answer
114 views

Hierarchical / tree data - which type of database to use & design?

For querying (and writing to) a hierarchical structure (like a folder containing files and other folders, up to n levels deep, where n is pretty small, probably around 10, and there would be lots of ...
1
vote
0answers
70 views

How can I store data with unpredictable data types in a table?

I'm currently working on a Headless CMS project and was wondering what approach would be the best for storing "fields" data in the database. Basically, users would be able to create a content model ...
1
vote
0answers
224 views

help in designing a database for medical clinic keeping record of patient visit

I am making software for patients record keeping in a clinic . I have written a peewee model for setting up tables to store record of each patient visit and patient contact and a relative table that ...
1
vote
0answers
310 views

Interesting Database Designs for Product Catalogue including a Recursive Approach

I am doing some work for a client and they are creating a database of products of which I need to consume product data when it's created and updated to store in a third party service and then present ...
1
vote
0answers
113 views

Double associative entity in a business environment involving Order Item Reviews?

Suppose you have Item (item_id, item_name) and Order (order_id, user_id, order_date) entities, with primary keys denoted in bold. Because an order can consist of multiple items, and an item can be ...
1
vote
0answers
37 views

Designing a message system

I want to create a messaging system but I'm stuck in the relationships between the sender/receiver of the message and the message itself. This is a list of requirements for this system: Allow both ...
1
vote
0answers
68 views

User permission businness logic

This question will also be posted in the stackoverflow, since dba and programmers may have different opinions. First of all sorry for the long question, but I wrote some extra code so that I can ...
1
vote
0answers
49 views

2 sets of multivalued attributes

I have multivalue attribute which I need to store in 2 different tables. It represents qualificiations for certain jobs. One table is for workers profiles and another for company. Company table states ...
1
vote
0answers
19 views

How to use validation rule within database with drop down menus.

Building a scheduling database using access 2013 for my company. I currently have it set up so that only employees that have a rating of 3 or more for the particular job show up when you use the drop ...
1
vote
2answers
158 views

Recommended Way to Store Logical Expressions

Say, I have the following SQL table: | ID | GameId | PlanId | MinAgeLimit | Amount | |:--------------|--------|-------------|-------:| | 1 | Cricket | P1 | 10 | 500 | | 2 | ...
1
vote
0answers
25 views

Is it correct/acceptable to have two FK pointing to the same table?

Is it correct/acceptable to have two FK pointing to the same table? In this business logic, a message can only be sent from one user to another. For example: CREATE TABLE user ( id INT NOT NULL ...
1
vote
0answers
331 views

Gaming shop database schema

I'm trying to design schema for my personal project that would be an online gaming shop. I would like to ask you guys for any suggestions/tips on how could I maybe improve my current draft. Concerns: ...
1
vote
0answers
21 views

How to model product part selection with restrictive rules?

I need to model database for product configurator where the user first selects a product template and adds features into that template. Available features need to be validated so that only features ...
1
vote
1answer
30 views

“Centralising” a “decentral” design

I have a web app using a database per client (around 120 and growing, 20k users in total). Each database has its own user table with user_id, username, password, etc. Meaning: if a user is member of ...
1
vote
0answers
38 views

Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] ...
1
vote
0answers
33 views

How do you aggregate the delta of values between two arbitrary datetime columns?

Please look at the columns and types of a table called History: - id PRIMARY KEY - account_id STRING - total_spent FLOAT - date_created DATETIME Sample data for this table: (id, account_id, ...