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.

923 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
84 views

One table or four?

I have four entities: Absence { ID, StartDate, EndDate, Days, Hours, Reason } Overtime { ID, Reason, Date (Integer), Hours (Integer) } Time in lieu { ID, Reason, Date, Hours } Holiday { ID, ...
3
votes
1answer
117 views

Schema for scarce goods

I have following objects: (Rooms) which are (available in a certain period of time [in days]). During the time of availability they can be (booked) by (users). There are basically these four tables. I ...
3
votes
1answer
394 views

Method to Obtain Lossless Join Boyce-Codd Normal Form (BCNF) Decomposition

I have been told a way to obtain lossless join BCNF but I don't know how to calculate candidate keys (also called super key[s] in some cases) and trivial dependencies. I have been given the ...
3
votes
0answers
61 views

How do I design a database for different sets of measurements on different subjects?

I'm conducting a research project that consists in sampling different physical quantities on about 30 buildings, most of them single-family houses. Some of those physical quantities will be common to ...
3
votes
0answers
3k views

SQLite Design: Better to have many tables with few columns or few tables with many columns?

I am struggling to figure out the best design for a Sqlite database that will hold data about Facebook pages so that I can calculate benchmarks for how the average Facebook page performs. I have an ...
3
votes
0answers
430 views

Replicating databases for local and cloud instances

Forgive me if this is an inappropriate place for this architectural question but I'm not sure where it best fits in the StackExchange family. I have been tasked with developing an online/live auction ...
2
votes
0answers
29 views

Variable layers of data in a multi-country addresses system

My diagram I want to record addresses in my database. The layout I have right now is visible in the diagram below: Considerations Continent, Country, Subdivision and City will be pre-filled, ...
2
votes
0answers
40 views

Normalization with two potential primary keys

I have the following STUDENT relation in first normal form, where id has been identified as the primary key (email here is also unique for all tuples): STUDENT(id, email, first_name, last_name) I ...
2
votes
0answers
38 views

Design issue in transitive dependencies between tables

This is a made-up scenario, but I imagine that it can be of general interest, so I'll post it here. Imagine the following business rule: A course has a grade scale that determines what grades a ...
2
votes
0answers
46 views

Aggregation Vs Ternary relationship

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 ...
2
votes
1answer
42 views

Database design - Members & companies represented by members

I'm struggling in database design problem and it seems I can't get it out properly. I need some help on this. Maybe it's a dumb case but I'd like to validate or correct my scheme and then go ahead. ...
2
votes
2answers
31 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 ...
2
votes
0answers
50 views

A multiple choice question, with multiple or only one correct answer, and sometimes no correct answer

It's a question & answer based model, and here is the scenario: A question with multiple choices given, with 3 to 7 correct answers among those choices. A question with multiple choices given, ...
2
votes
0answers
92 views

What's the more performant database design for query speed and write overhead when storing many boolean columns?

Setting the scene I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. ...
2
votes
0answers
87 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-...
2
votes
0answers
29 views

How to handle normalizing the concept of a grid, and also plan for data versioning?

I have inherited a database that has a normalized structure, except for its data tables which are aggressively denormalized. Our system mostly renders grids into a browser, and the previous developer ...
2
votes
0answers
47 views

How To Query Databases in Subject-Predicate Format?

I have csv data in this format: id fieldname fieldvalue -- --------- ---------- 1 fieldA A1 1 fieldB B1 2 fieldA A2 2 fieldA B2 If I upload this to MySQL, how can I ...
2
votes
0answers
41 views

Basic company and user registration database - Pulling my hair out

I have now created a partially working database, although I feel I have over complicated things, and also, not sure where to add the extra tables/relationships required. First off all, I have a ...
2
votes
1answer
41 views

One vs. Many Tables for Frequently but Asynchronously Updated Columns?

As part of my Postgres 10 schema, I have two tables; current and historic -- both have the same columns. current has about 20 (+/-) columns, and holds the most up-to-date information for accounts. I ...
2
votes
0answers
70 views

Non-standard number data types in Oracle

I'm not sure that this question belongs to this StackExchange. I've asked this question on StackOverflow: Besides the "usual" number data type where precision is greater then scale there are ...
2
votes
0answers
30 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 ...
2
votes
0answers
41 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-...
2
votes
0answers
52 views

Is this “idiosyncratic” method to manage constraints declarations beneficial?

I just wanted to get feedback from actual DBAs, based on their experience and knlowledge of database theory, in regards to this. At my company, we have a large .sql file that holds all of our schema ...
2
votes
0answers
159 views

Modelling a database for a simple IT device tracking scenario

Sorry, my English is not that good. I need a simple IT device tracking sytem at work. For each device I need to know its current location, status and ownership (employee to which it is assigned). ...
2
votes
1answer
91 views

How to store x,y graphs: (a) Using x,y columns for each point, or (b) to store the set of points as comma separated strings?

We are designing a database to store the X,Y graph data of electronic devices apart from other entities. Business domain description We have a collection of objects A, each object A has a collection ...
2
votes
0answers
510 views

Database design for Subscription, Coupons and Plans

I have searched a lot in SO and SE but I did not get anything particularly about this kind of database design with coupon codes included. Please take a look at this problem. I am developing a website ...
2
votes
0answers
47 views

Needing some guidance on designing a DB

So I am not a coder, clearly. I'm hoping that someone would be kind enough to understand my situation and help me ask the right questions to have a DB designed for my needs. I currently use Excel to ...
2
votes
0answers
53 views

Setting up a Grand Jury relational database structure

I'm designing a database to store information about a grand jury. I'm wondering if my tables are normalized, and if they structure makes sense. A report is sent in for review by grand jury. Then a ...
2
votes
0answers
45 views

Can a database for teams, activites, markers and outputs be structured without data redundancy?

There are many TEAMS that engage in ACTIVITIES in a many-to-many relationship with TEAM_ACTIVITIES as a joining table. Each ACTIVITY has a set of MARKERS that are used to track each team's progress in ...
2
votes
0answers
57 views

Surrogate foreign keys allow inconsistent data - which normal form does this violate?

We have inherited a system which has surrogate keys on every table (that is, an auto-increment primary key, id). All of the foreign key constraints are on individual surrogate keys, and this seems to ...
2
votes
2answers
96 views

Difficulty Designing A Product Database

The image below is my ham-fisted attempt to design a database of cabinets. I'm new to this and this is simply an exercise to help me understand database structure. My goal is not to create an ...
2
votes
1answer
38 views

Database Design for a Wizard's Output

I'm not necessary sure if the "wizard" is the correct term for it, but essentially what I have is a form that have a specific set of questions that a user must answer in order to determine which ...
2
votes
1answer
68 views

Questions Concerning the Chase Test

For the question above, my work for each of the questions is below the concerns. Here are my main concerns: Does the order in which you use the relations matter? Can you end up with less tuples with ...
2
votes
0answers
94 views

Where does the 'Status' field belong?

I have two tables, let's call them leave_request and the other one leave_given. The employee submit a leave_request to take a vacation where he has to fill place of vacation, date of start, date of ...
2
votes
0answers
46 views

Design for a child to have a single parent `A` OR `B` with FK cascade constraint?

I'm assuming this is not possible with PK and FK... Let's say I have tables A, B, and C. A and B are "parents", and B and C are "children" A is the root B can be the child of either A or B C ...
2
votes
1answer
44 views

How to Store Trees on Disk

I am wondering how to store trees on a physical disk, for perhaps a tree-oriented database like an XML database. Wikipedia states about XML databases: ...custom optimized data structures are used ...
2
votes
0answers
1k 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 ...
2
votes
0answers
81 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 ...
2
votes
1answer
62 views

Assessing the normal form for a relation with a multi-valued attribute

Assume that you have designed the following schema of relations: COURSES (CID, CNAME) STUDENTS(SID, SNAME, FAV_CIDS) However, you can not have a relation with set valued attributes (Did not really ...
2
votes
1answer
221 views

Suggestion for a contact management database design

I am currently designing a contact management database for a chamber of commerce. The goal of the database is to store all the person (except our own staff), all recorded companies (regular companies ...
2
votes
0answers
1k views

NoSql - Dynamo db - when do you need multiple tables?

I'm experienced in RDBMS, but new to NoSql. This aws dynamodb design advice says that "most well designed applications require only one table". I'm trying to wrap my head around that. I don't see a ...
2
votes
0answers
42 views

Modelling a scenario involving architecture firms projects

I'm very new to Database Modelling (using the Finkelstein methodology) and I'm getting confused on a modelling scenario in particular. I am able to draw out the basics, however am running into some ...
2
votes
0answers
40 views

Can I create several foreign key constraints between 2 table?

I am trying to build a database of recipes. The problem I am facing is that my recipe book also contains recipes for some of my ingredients (a.k.a. intermediary product) Instead of the following ...
2
votes
0answers
49 views

SQL Server database design with foreign keys

See the following link for the (total) databse design: https://sqldbm.com/Project/SQLServer/Share/_AUedvNutCEV2DGLJleUWA I am not used to the database design first and then build it. But... for ...
2
votes
2answers
47 views

Schema For “Other - Add Comment”

Many user forms contain an option for "Other - Add Comment" where the user can provide an item not found in the given list. In other words, the developer sets the list of items. The user must either ...
2
votes
1answer
81 views

Could I get some feedback on my first normalisation design?

Im creating a database for a project with Olympics as my case study. I have drafted a rough table storing information I would like to store and display in my database below (an unnormalised table): I ...
2
votes
3answers
266 views

How to set up login/guest and checkout/add to cart

I want to create an online store database. I want to give users the ability to 'add to cart' and 'check out' without login. My idea is that if the user logs in, then I can save the information in ...
2
votes
0answers
72 views

How to design normalized relations to store “global/generic” data?

Context I was designing a relational database model to store data about a company and its production facilities. I had a facility relation and many other relations referencing it, most of the time ...
2
votes
0answers
228 views

Is it correct to model a specialized table using a foreign key whose referencing attributes are not also forming its primary key?

In the process of designing a database model, I would like to implement a Class Table Inheritance pattern in a way that does not seem common at first. I would like to know if it is a correct ...
2
votes
1answer
91 views

Database Design when Admin and Employee have similar roles except Admin can see all the other Employees Data

We are building a system with concept of Admin and Employee. so basically Admin is an employee with all powers and can view all the data created by other Employee. CREATE TABLE `Vendor` ( `...