Skip to main content

Questions tagged [database-design]

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

1,271 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
13 votes
1 answer
17k 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): ##...
Rathish Kumar B's user avatar
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 ...
Amater90's user avatar
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 ...
wchargin's user avatar
  • 171
6 votes
0 answers
667 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 ...
jeremieca's user avatar
  • 177
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 ...
WordPress Speed's user avatar
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 ...
Sagar Karwande's user avatar
5 votes
0 answers
595 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 . ...
Devin's user avatar
  • 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 ...
Evan M.'s user avatar
  • 231
4 votes
0 answers
279 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-...
Dilame Nickolson's user avatar
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-...
Mukul Mantosh's user avatar
4 votes
0 answers
137 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 ...
Jarrette's user avatar
  • 149
4 votes
0 answers
319 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 ...
student_1123's user avatar
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-...
navinpai's user avatar
  • 141
4 votes
2 answers
1k 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 ...
applaps's user avatar
  • 41
4 votes
1 answer
900 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, ...
user329488's user avatar
4 votes
4 answers
13k 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 ...
user3832583's user avatar
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 ...
aacom's user avatar
  • 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 ...
Juan Carlos Coto's user avatar
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 ...
gab's user avatar
  • 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 ...
Michael's user avatar
  • 173
3 votes
0 answers
82 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 ...
Kenny Read's user avatar
3 votes
1 answer
154 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 ...
DataBasedxd's user avatar
3 votes
0 answers
40 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<...
eimaj's user avatar
  • 31
3 votes
0 answers
1k views

How to design a database schema for multiple user types?

Question: I have a system where I can have multiple types of users: buyers, vendors, admins, etc. These types of users aren't identical, but they're not utterly unlike each other. There will be ...
the thinker's user avatar
3 votes
0 answers
53 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., '...
KP7's user avatar
  • 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, ...
Junius's user avatar
  • 31
3 votes
2 answers
269 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 ...
d-_-b's user avatar
  • 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 ...
yurtesen's user avatar
  • 237
3 votes
0 answers
371 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 '...
Jeff Lowery's user avatar
3 votes
0 answers
267 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 ...
Emily Horsman's user avatar
3 votes
0 answers
162 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→...
Roey Waitsman's user avatar
3 votes
0 answers
120 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 ...
Jason Young's user avatar
3 votes
1 answer
65 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 -- + ----- + ---- + ----- + ---- ...
aaroncatlin's user avatar
3 votes
0 answers
197 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 ...
Gamma's user avatar
  • 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 ...
thehnrytrapezoid's user avatar
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 ...
SS''s user avatar
  • 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 ...
eric's user avatar
  • 143
3 votes
0 answers
828 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 ...
justinb's user avatar
  • 31
3 votes
0 answers
770 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 ...
Florian's user avatar
  • 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')), ...
Vasek's user avatar
  • 31
3 votes
0 answers
228 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, ...
pigpen's user avatar
  • 31
3 votes
0 answers
102 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 ...
Cameron Sumpter's user avatar
3 votes
1 answer
71 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 ...
corykeane's user avatar
3 votes
0 answers
877 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 ...
Juan Manuel Rodriguez A.'s user avatar
3 votes
0 answers
487 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 ...
Alok Patel's user avatar
3 votes
0 answers
797 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-...
alexw's user avatar
  • 192
3 votes
0 answers
293 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 ...
Charlie's user avatar
  • 91
3 votes
0 answers
499 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, ...
GeraldBiggs's user avatar
3 votes
0 answers
646 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, ...
rap-2-h's user avatar
  • 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 ...
Peter M Taylor's user avatar

1
2 3 4 5
26