Questions tagged [database-design]

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

1,017 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
1
vote
1answer
23 views

Its necessary to relate the ComplaintMessages entity with the User and Admin entities or is enough to relate with the Complaint entity? (ER Model)

I'm in doubt in the ER model of a scenario where a user can make complaints. The user enters the title and the description of the complaint and submits the info. A site admin can see all user ...
1
vote
1answer
22 views

An efficient database design for recording complex contact/configurator form

I know it's a quite generic question and probably opinionated. I don't know where to ask. Maybe you could point me to the right direction. Currently I have a very big 'configurator', 10 steps long, ...
1
vote
1answer
95 views

DB Design - Assigning users to multiple sites with different roles

I am working on a DB design that lets me manage roles and permissions for each user per site. These are my business rules: A user can manage multiple sites A user can only have one role per site A ...
1
vote
1answer
32 views

Mongo Cluster sharded replica cluster design question

I have 5 old servers, each with single socket CPU, 4 physical cores(8 logical) and 128GB of RAM. I am getting started with mongoDB and plan to deploy MongoDB 4.2.2 on my 5 servers for a lab ...
1
vote
0answers
30 views

Schema design for an entity with 'heterogeneous' status types

Consider a notification system which gets some event data and list of business email addresses as input (the list is basically computed by looking at which users/addresses are subscribed to which type ...
1
vote
0answers
29 views

Vote based approval system in DBMS

I try to create a system to auto approve proposed posts by popular vote. I am currently evaluating what portions of this I can do within the DBMS and if it is sensible. I use PostgreSQL and I can go ...
1
vote
0answers
30 views

Parent child relation with One to One

We have a table called Contracts which has a lot of columns. Some columns are for employees, some columns are for managers, some columns are for locals and so on. Since the table is growing, that's ...
1
vote
0answers
18 views

Modeling a system with optional components

I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on ...
1
vote
0answers
38 views

Database Design Strategy Help

Thanks for reading this. I’ve completely overthought this so I’m hoping to get some feedback. I have a customer table (custID, statusID), a notes table (custID, noteID), an action table (custID, ...
1
vote
1answer
39 views

Database Model for Various Type of Sensors

I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this? - Option 1 : should I integrate all ...
1
vote
1answer
181 views

Finding all possible minimal covers

I have a relation schema R = {A, B, C} and the following functional dependencies: A → B A → C B → A B → C C → A C → B How many different minimal covers can I derive from this relation schema? I have ...
1
vote
0answers
118 views

Schema for social network using the supertype/subtype pattern

Scenario I'm trying to come up with a schema for a kind-of social network app, where users can publish Posts (and photos inside Posts), and also manage a private Diary that has also Posts (not sure ...
1
vote
0answers
51 views

Creating a time-series database for a power plant scenario

Considerations I need to create timeseries tables in a SQL database, which will hold data about power generated by a power plant. I need four columns there: ( power_plant_id int FK, ...
1
vote
1answer
68 views

UUID as primary key and download ID

I'm designing a Django Rest Framework API with PostgreSQL. In the database I'll be recording information about documents which can be requested for download. I want the request to include a unique id, ...
1
vote
0answers
52 views

Combine sales data without knowing the number of sold units per one sale

I’d like to consult for a strategy plan of a database structure for the following scenario: My app is for offices buildings sales reports where a building is the basic unit that I need to generate ...
1
vote
0answers
81 views

ER Relationship Total and Partial Participation Example

I am trying to understand partial and total participation. Given the statement: "Each restaurant must have one or more dishes and each dish must be provided by exact one restaurant." The below ...
1
vote
0answers
75 views

Is EAV a good choice for scenario when the number of fields may differ client to client?

Suppose that you are developing an application that could be used by multiple clients. You have to store "basic details" of an employee of a company (your client) into your database. The first 15 ...
1
vote
0answers
44 views

Linguistic corpus design: order elements of different types

I'm trying to design a database for a linguistic corpus search engine. Basically there is a manuscript which contains several songs, and every song in turn contains stanzas and proses. Since stanzas (...
1
vote
1answer
64 views

Understanding many-to-many ternary relationship sets

I am new to entity-relationship diagrams. I'm a bit confused when it comes to interpreting ternary relationship sets such as this: Does this signify that every relationship instance of Party will ...
1
vote
0answers
46 views

Room bookings database - does it make sense to create two tables instead of one column?

There are two types of entities: user and room. A user can book room select room unbook room unselect room "Selected room" is a state that indicates when a user has decided to book a room and so he/...
1
vote
0answers
63 views

verifying non-additive join decomposition

I am reading a DMBA book and have come across non-additive (lossless) join property. The algorithm for decomposition into multiple tables is borderline useless for practical work, so I am frequently ...
1
vote
2answers
122 views

Mandatory and non mandatory attributes for products

I am trying to come up with a simple way of implementing the following design, but I always end up with something overly complicated. I am trying to design a database where companies can store ...
1
vote
0answers
114 views

Mysql Workbench generates an additional FK in child of a child table referencing the Parent of its Parent

I'm trying to create a Part that is a child of Sublot, which in turn is a child of Lot. Mysql Workbench automatically creates the foreign keys, when I insert the many-to-one relations graphically. ...
1
vote
0answers
46 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
47 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
61 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 have ...
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
836 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
51 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
37 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
1k 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
316 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
48 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
33 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
0answers
54 views

Schema: Table with many rows vs single table with many columns

I'm torn between two different schemas and would love some of your thoughts. Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 ...
1
vote
0answers
56 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
33 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
22 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
323 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
86 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
74 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
45 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
2answers
39 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
0answers
195 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
66 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
22 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
226 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
34 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
47 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
3 4
5
6 7
21