Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
1,235
questions with no upvoted or accepted answers
13
votes
1
answer
16k
views
Conceptual data model for a billing system
I am working on an existing MySQL database. My task is to create a new data model for a billing system on that existing application.
I have created a conceptual data model (click to enlarge):
##...
- 2,274
9
votes
0
answers
4k
views
Working on a decision tree database design
I'm doing the conceptual model for a Decision tree database. The goal is store any Decision tree.
I have four entity types: DecisionTree, Node, Branch and Leaf.
Each Decision tree is composed of ...
- 91
7
votes
2
answers
1k
views
Foreign key to multiple tables via forwarding table
Problem statement
I have a table with a column whose values are foreign keys, but the
target table of the foreign key differs from row to row. The relevant
table can be determined from the key value ...
- 171
5
votes
1
answer
3k
views
ERD for a web development company
I am working on an ERD which represents the relationships between entities in a Web development company behavioral model.
There I have entities like:
Customer
Service ----------- (like: web ...
5
votes
0
answers
11k
views
Product db association for e-commerce
Terms related to E-commerce
In ecommerce setup we have Products and Variants.
Variants are also products but they vary on some attributes.
eg: A T-shirt can vary on Size and Color attributes.
Let ...
- 151
5
votes
0
answers
582
views
Is setting a foreign key as primary key in a One-to-One relationship a good idea?
I have a table user and a table user_statistic with a one to one (1:1) relationship.
Note that we have an identifying relationship between user and user_statistic.
Which is best practice for ...
- 167
5
votes
0
answers
575
views
Storing a symmetric matrix in postgresql
I have several matrices of distances between objects, like this:
Method 1
A B C D ...
A 0.0000000 . . .
B 0.5458965 0.0000000 . ...
- 193
5
votes
2
answers
1k
views
Should I snowflake or duplicate it across my facts?
I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas.
In my warehouse, I've got two different fact tables that tracking daily changes ...
- 231
4
votes
0
answers
254
views
Accounting database model
Need to build a service with an internal wallet function.
Here is a simple use-case diagram:
I have made a DB model
But I don't like it for the next reasons
No guarantee there will be only 1 sub-...
4
votes
0
answers
1k
views
Database Design for tracking user flow
I want to design a user tracking system between different tables/models. I want to track the user's progress. There is a Module table which is related to sections and sections are related to sub-...
- 141
4
votes
0
answers
135
views
Designing golf pro scheduling into POS
I have an existing Point of Sale app that is used for golf courses. I have a new feature request from one of my courses that would allow users of my POS to sell golf lessons to customers. Here are ...
- 149
4
votes
0
answers
286
views
Can the Chase test algorithm only modify attributes on the right side of a functional dependency?
I'm attempting to apply the Chase Algorithm to determine if a decomposition is a lossless join decomposition.
Functional Dependencies = {A → C, B → D, C → D, DE → C, CE → A}
Here is my initial ...
- 41
4
votes
0
answers
1k
views
How costly is changing column datatype in PostgreSQL?
I'm currently evaluating PostgreSQL for an app we are developing which comes with a weird usecase. We want to allow clients to create new fields which in turn will create new columns in a client-...
- 141
4
votes
2
answers
989
views
Entity-Relationship Diagrams: Identifying which way a relationship flows
I am having trouble grasping the "relationship" concept of an entity-relationship diagram. For example, you have Classroom and Furniture as your entities. If you were to put a relationship diamond ...
- 41
4
votes
1
answer
856
views
Tables for Product Components AND Component Combinations
I have a basic understanding of relational databases and would like to assign a number of attributes to an assortment of products. Products examined consistently have four components: model, fabric, ...
- 41
4
votes
4
answers
12k
views
How can I model a medical scenario in an entity-relationship diagram?
I am a self-taught frontend developer that is trying to go full-stack, so I am trying to teach myself data modelling by means of working on some exercises out of a database management book.
In this ...
- 141
4
votes
2
answers
67
views
Snooping-safe Vote tables
I have a table with both user_id and vote. They are stored together so each user can only give one vote. However it's also problematic because admins can simply read who voted what. Is there a way to ...
- 57
4
votes
0
answers
1k
views
How to properly configure pgBouncer transaction level pooling?
What is a good way to determine the number of available transaction connections when using pgBouncer to pool database connections? Also, how should I determine the number of max_connections and ...
- 1,558
4
votes
0
answers
237
views
Database describing databases and references to tables
Context
The company I work for makes a number of different softwares each with its own database. As they're all about related things (accounting) a good amount of data is theoretically the same among ...
- 155
4
votes
0
answers
1k
views
Dynamic MySQL partitioning based on Unix Time
My DB design includes multiple MYISAM tables with measurements collected online,
Each row record contains auto-incremented id, some data and an integer representing Unix time.
I am designing an ...
- 173
3
votes
0
answers
59
views
Best practices for heavily coded data in a relational database and lookup tables
I'm working on converting a legacy system into a more modern web application and I keep finding myself tripping over normalizing the data.
The dataset is quite large; about 30 tables with around 15 ...
- 31
3
votes
1
answer
70
views
Need help with first ER diagram
This is my first time doing the diagram. My teacher isn't the best at helping out or responding to questions so I've come here to ask for further assistance. I think I've mostly got it correct, but ...
- 31
3
votes
0
answers
37
views
Data modelling for dynamic grouping of rows in Cassandra
I have a feed of game events with a schema like;
{
"gameId": text,
"playerId": text,
"eventId": text,
"ts": bigint,
"metrics": map<...
- 31
3
votes
0
answers
50
views
Help Creating a 'Complex' ERD
I am stuck on how to represent the relationships I need correctly. I have a
Produce table. Each type of produce—e.g., 'lettuce'—has one or more quantity suffixes that can be associated with it, e.g., '...
- 39
3
votes
0
answers
70
views
Foreign key massacre: How many tables, Winston?
I'm designing a feature related to defining insurance policies.
There's table of Provisions that have names and IDs. Anything called an "ID" here is a surrogate key in a table.
We then have Forms, ...
- 31
3
votes
2
answers
233
views
How to design a database for search history with multiple facets?
I want to show users a history of their searches, which can often be complicated:
any of these terms: [a,b,c]
all of these terms: [d,e,f]
not these colors: [h,i,j]
Since these searches are done ...
- 131
3
votes
0
answers
3k
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 ...
- 227
3
votes
0
answers
322
views
Versioning rows in a table
So I have this concept in my head of "immutable" records, which I am sure is not novel, but can't seem to find the right google keywords.
Basically, historical row values need to be preserved as '...
- 131
3
votes
0
answers
253
views
Is it correct that functional dependencies are not symmetrical?
Say we have a relation R(A, B, C). We have one known functional dependency: A → B. I'm wondering if it's true that there would be no way to prove that A → B implies B → A. If it was true, I can't ...
- 131
3
votes
0
answers
158
views
Third Normal Form (3NF) Decomposition Scenario
I am trying to understand what are the steps to check if a decomposition of table is in 3NF.
There is a specific exercise I'm trying to solve with no success.
Given that R={A1,A2,A3,A4}
and F={A1→...
3
votes
0
answers
103
views
How to efficiently store 672 bits of information in PostgreSQL
I have a requirement that needs to store lots of bits and it's actually a time setting for every 15 minutes in a week (indicating if a user is free during that time).
So for one day we need: 60 ...
- 131
3
votes
1
answer
64
views
Ensuring Uniqueness Across Columns
I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution!
Consider the following table:
ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
...
- 437
3
votes
0
answers
171
views
How to design a database for my entity-relationship diagram about Projects, Tasks and Developers
I'm new to database and my entity-relationship diagram is as follows:
According to that I designed my SQL-DDL database statements as shown below:
CREATE TABLE Developer (
DeveloperID int NOT ...
- 131
3
votes
0
answers
1k
views
Modeling a database structure for a trip management business domain
I am sketching up a database design and it is giving me some troubles. Basically something just "smells" about this design but I can't seem to arrive at a better way to do it. For example, the Joins ...
3
votes
0
answers
7k
views
How do you perform the chase test?
Trying to follow the chase algorithm as I understand it,
If two rows agree in the left side of a functional dependency (FD for brevity), make
their right sides agree too.
Always replace a ...
- 155
3
votes
0
answers
2k
views
Serialized Inventory Tracking
I'm developing a inventory system with optional serial no tracking (which users can turn on for items they select).
in order to keep stock records currently i came up with two options.
Option 01
use ...
- 43
3
votes
0
answers
785
views
Which SQL Power Architect type to specify for [PostgreSQL] UUIDs/GUIDs in new column property template/UI?
I have inherited maintaining and extending SQL Power Architect projects in source control which are used to generate the DDL for project DB schemas. We are currently using PostgreSQL 9.5 and SQL Power ...
- 31
3
votes
0
answers
738
views
enforcing at least one child in a foreign key constraint (or 1:1..n relationship)
While trying to implement multi-language strings in a postgres DB, I came across "Data Modeling for Multiple Languages (on Vertabelo)". Especially "Approach #4", splitting non-translatable properties ...
- 133
3
votes
0
answers
2k
views
Supertype table with disjoint subtypes in MySQL
I want to have base parent table with few disjoint subtypes. To illustrate what I mean:
CREATE TABLE issues (
issue_id SERIAL PRIMARY_KEY,
issue_type CHAR(1) CHECK (issue_type IN ('B', 'F')),
...
- 31
3
votes
0
answers
217
views
Conceptual Diagram advice for varying Product and History states
AIM:
A MsSQL DB to hold Products / services that will vary significantly, although there won't be many; hence, the N:N attributes for ProductTypes.
Be able to keep a record of Product changes [Price, ...
- 31
3
votes
0
answers
96
views
Optimality of Columns vs. Complementary Table in MySQL
I'm starting work on a project where I'll have a table of entries as well as a table of descriptors.
The entries will be updated infrequently, if ever, and the descriptors table will be largely ...
- 173
3
votes
1
answer
70
views
When to create a new table if functionality is the same?
I am having trouble making database schema/design decisions. The database I am building is relational.
Consider this example:
I have these tables: orders, products, companies.
I want to create ...
- 31
3
votes
0
answers
847
views
How to model a price discount with a purchase of several products as condition?
Scenario
Let us say that, in my business context, there are three different products, i.e., product one, two and three, and I wish to offer price discounts when a condition like the following one ...
3
votes
0
answers
475
views
Is it good practice to have multiple keyspaces in Cassandra?
I've Cassandra configured on Amazon EC2 having 3 nodes(instances) in single cluster. Now what I want to do is give some space on Cassandra to my clients by creating separate keyspace for each in a ...
- 79
3
votes
0
answers
766
views
Anchoring values for a rating or likert scale
I am designing a schema for an interview system as per the suggestion in this answer. I need to store several different types of questions:
Questions with a free-text response
Multiple choice text-...
- 192
3
votes
0
answers
279
views
Inputting auto-incrementing, grouped, unique hashes?
Alright, here's what I've come up with so far:
SET @id=(SELECT IFNULL(MAX(id) + 1, 0) FROM leads);
UPDATE leads SET id=@id WHERE id IN (
SELECT id FROM (SELECT * FROM leads) AS something WHERE ...
- 91
3
votes
0
answers
495
views
Database Designs Alternative to Polymorphism in Establishing Relationships
I'm working on a database which handles replacement of parts in a machine.
Rules
A machine has many components
A machine has a set of assembly instructions
Many components have many part (screws,
...
- 131
3
votes
0
answers
548
views
Could I use both UUID and Sequential ID or is it bad design
Sequential IDs have pros and cons. UUIDs have pros and cons too. Why not use both for each table? (Let's say I have -and will have- no problem about storage space)
I could use:
UUID for exposition, ...
- 181
3
votes
0
answers
1k
views
Personal Finance database review
G'Day,
I found that someone had started on this topic on personal finance, I like to go further using a good personal finance plan I found on the internet which I would model. In advance please ...
3
votes
0
answers
1k
views
ERD for a Contract between a Writer and a Publisher: Is it correct, can it be simpler?
I created an entity-relationship diagram (ERD) for a book publishing business context that I will detail bellow.
Entity types
The entity types of interest are:
Writer
Work (as a book)
Publisher
Split ...
- 141