Questions tagged [database-design]

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

1,112 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
11
votes
1answer
14k 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): ##...
9
votes
1answer
485 views

Efficiently store large list structure in RocksDB so that the data can be retrieved in pages

Description: RocksDB is a key-value storage so we can simply serialise the list of objects & store the value corresponding to a key. This would be ok if the data in the list is small enough. ...
8
votes
0answers
3k 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 ...
5
votes
1answer
2k 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
0answers
91 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-...
5
votes
0answers
8k 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 ...
4
votes
0answers
597 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-...
4
votes
0answers
103 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 ...
4
votes
0answers
143 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 ...
4
votes
0answers
503 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-...
4
votes
0answers
184 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 ...
4
votes
0answers
455 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 . ...
4
votes
1answer
571 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, ...
4
votes
4answers
9k 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 ...
4
votes
2answers
96 views

Best db setup to accomplish yes/no list of clients for daily calls

I will preface this by saying I am a relative novice to Access but I have managed to develop some basic databases. I have some working knowledge of expressions and macros but VBA is still a bit of a ...
4
votes
2answers
62 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 ...
4
votes
0answers
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 ...
4
votes
0answers
234 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 ...
4
votes
0answers
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 ...
4
votes
2answers
891 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 ...
3
votes
0answers
68 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, ...
3
votes
0answers
1k 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 ...
3
votes
0answers
103 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 '...
3
votes
0answers
109 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
0answers
75 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 ...
3
votes
1answer
54 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 -- + ----- + ---- + ----- + ---- ...
3
votes
0answers
53 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 ...
3
votes
1answer
296 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 ...
3
votes
0answers
604 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
0answers
4k 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 ...
3
votes
0answers
1k 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 ...
3
votes
0answers
644 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 ...
3
votes
1answer
282 views

Relationship between two multi-valued child tables

Scenario In my system, there are a number of different TradeBaskets. Each TradeBasket contains a number of different Securities. The Securities have a Type, Value, Sector etc... and new Securities ...
3
votes
0answers
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')), ...
3
votes
0answers
157 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, ...
3
votes
0answers
82 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 ...
3
votes
1answer
65 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 ...
3
votes
0answers
723 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
0answers
356 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 ...
3
votes
0answers
579 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-...
3
votes
0answers
227 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 ...
3
votes
0answers
393 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, ...
3
votes
0answers
325 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, ...
3
votes
0answers
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
0answers
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 ...
3
votes
0answers
107 views

SQL Server storing predefined value sets along with open values

I have Cars with different Characteristics, Characteristics have values, which can be restricted by predefined set of values or be "open" values (any string). Characteristics and predefined values ...
3
votes
0answers
128 views

How to relate a "parent common" table with its (possibly many) "subtype" tables?

I am having a bit of a brain freeze. I have a table whose properties are the root of four distinct elements, for example: I need to relate SystemType to a SystemCommon, where one SystemCommon can ...
3
votes
1answer
66 views

What process would you use to determine references on a database that has no integrity?

Imagine you inherit a database which has a bunch of tables, no documentation and zero referential integrity. Now you're trying to find out what are the various relationships between different tables. ...
3
votes
0answers
78 views

why some one will use approximate data types for sales

I was messing around with Kartris online shop and I was reading this definition for a product version(you have basic information of a product in one table and many versions of products wich can have ...
3
votes
0answers
672 views

transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

I have created the following indexed view and its clustered index: This view was created on a subscriber database and all the tables used in the view are constantly being written to through the ...

1
2 3 4 5
23