Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [database-design]

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

1
vote
3answers
49 views

Maintaining data integrity when one table has multiple relationships, but only one should be used. (is this bad design?)

I'm working on a database that holds laboratory medical testing orders. The issue I have (maybe) is that I have a table that holds an order number, and a test code. There is also a TestNumber PK. ...
1
vote
0answers
37 views

MySQL Query Optimization Group By with Max [closed]

I have posted same question on at stackoverflow.com but I did not get any structural solution so I am trying to get answer on dba specific community. Trying to optimise MySQL query which uses Group ...
0
votes
0answers
14 views

Convert IS A relationship into relational schema [duplicate]

I'm trying to map below IS A relationship into relational schema. Would there be two tables one for Savings account and one for Checking account each containing their own attributes? Or would there ...
0
votes
0answers
25 views

Indexing strategy for date range availability in SQL [duplicate]

Imagine the structure of a simple hotel booking app: hotel_chain(id, name) hotel(id, hotel_chain_id, name) room(id, hotel_id) booking(room_id, date_start, date_end) There are hotels, that belong to ...
0
votes
1answer
22 views

MySQL - Maintain History of Records but Using Most Recent

Let's say I'm developing a database for a transportation service which owns certain vehicles and hires drivers which are assigned to specific vehicles. The database comprises of the following tables: ...
-1
votes
1answer
23 views

How to store data [closed]

I have an app which records Insurances in a company where they work. each insurance has a time frame. Example : Employee John decides to buy a medical insurance from their employer. But the next ...
2
votes
0answers
24 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
2answers
41 views

What is better - having 10 databases for 10 organisations or 1 table for 10 organisation with org_id? [duplicate]

I am making a web service (using Laravel in case that is relevant) that will have many separate organisations generated by the service. Considerations I can manage all organisation data in one ...
-1
votes
1answer
37 views

One Product, Multiple Categories (one to many) too many rows on table

So I'm developing an e-commerce website, and I have a product with many categories. So my concern is, what if I have 1000 products and each of them has 5 categories, so is it unavoidable to have ...
0
votes
1answer
59 views

How I should design tables for transaction (payment) for banking?

My team and I are trying to design a new simple database system to manage transaction of customers who use their debit cards. These are some problems we haven't yet decided: When it comes to 'payment ...
8
votes
3answers
174 views

How can I get running totals of recent rows faster?

I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for ...
0
votes
1answer
25 views

How to multi-match two tables efficiently?

I'm relatively new to database-design and want to create a SQLite database for an application. The application needs to handle lots of objects and assign them to a smaller list of categories. Both ...
0
votes
0answers
27 views

Does “Inheritance” Make Sense with This Requirement?

I'm using Oracle 12c. Given an existing table (we call it TBL_M), and based on the criteria below, I was wondering if using inheritance makes sense to lay out the entities: Information for one group ...
0
votes
1answer
34 views

Proper creation of a database with categories that have sub-categories

Preface: I have for the most part never worked with databases, and am new. So my terminology may be incorrect at times. I was wondering what the correct procedure would be for creating a database ...
0
votes
1answer
24 views

How to PARTITION a table in mysql?

I read a lot of articles that PARTITIONing is not helpful in most cases. I believe it should speed up the queries in my case. I have a table with the structure of CREATE TABLE pages ( page_id int(11) ...
-1
votes
1answer
44 views

How to normalize CSV invoice data?

For flat data which comes in with fields like: name address status class version user mode CSV files are regularly received, with the above as typical columns. ...
0
votes
0answers
15 views

Cardinality Ratios for Quartenary relations

I'm making a database with a design that includes 4 entities being in a relation with each other but I'm not sure how to work out the cardinality ratios for each of the entities (like what's the logic ...
0
votes
1answer
34 views

Can multiple fact tables have the same structure? [closed]

I'm designing the schema of a data base and I have two solutions : the first is to use one fact table linked to multiple dimensions. the second is to use multiple fact tables with same structure for ...
0
votes
1answer
49 views

Creating and querying a database to set hierarchical associations between Groups, Users, Items and Points

I am working with three entity types, Group, User and Item. A Group consists of Users, and a User consists of Items. Now, I have to work with a fourth entity type: Point. I want to be able to ...
0
votes
1answer
55 views

Will this Database design logic work?

I am currently working on structuring out a database in MySQL Workbench to keep track of expenses for Truck drivers that would be turned into an application using Java. Objective: Create a report of ...
0
votes
1answer
52 views

Proper database structure for storing multiple foreign keys

I am using MySQL 5.7.25 and need to store multiple foreign keys for multiple columns. Scheme of my table: CREATE TABLE `tours` ( `id` int(10) UNSIGNED NOT NULL, `countries` text NOT NULL, `...
0
votes
0answers
30 views

Creating a Database for Multiple Calendars Support

I am trying to create a calendar application that can support multiple calendars, for instance Indian, Nepali, Russian etc. Users can enable or disable the calendars according to their needs. I have ...
-1
votes
0answers
14 views

Database design implementing Notifications using multiple foreign keys, accepting Nulls/None

I have the following Entities: Account, Owner_TA, Owner_TB, Pub, Item, event, RTX Owner_TA and OWNER_TB have Foreignkey to Account Pub and Event have a Foreignkey to Owner_TB Item has a Foreignkey ...
0
votes
1answer
66 views

Which one of these two contact management database designs is correct or best practice?

In this first database design, column name starting from first_name to country exist in each table. The relationship of each table to table user_categories is ONE TO MANY. In this second database ...
0
votes
2answers
39 views

I have one table for users, one for a collection of games. My users can own one or many games. How do I structure that in my database?

Since I can't store lists, should I add a column to my table called owned games and then just keep a long string like "game1, game2..."? Or should I add another table called something like ownership ...
0
votes
0answers
20 views

Self-referencing table vs Mutiple table approach

I am designing a quiz database and I have to incorporate categories into the questions. I (think) that I have a hierarchical structure that lends itself to a self-referencing table (I have only just ...
-1
votes
0answers
10 views

Design question regarding location and DRY principles

I have a client that handed me some tables to create an ms access db from. The design irritates me and I think a different way would be better. Area (table name) City State Addressln1 5 other ...
1
vote
0answers
29 views

Foreign keys pointing to “look-up” tables: Should we reference Id or Code values? [closed]

We are rewriting from a huge monolithic application to smaller application using rest. While the old application had one huge tables called REFERENCE tables with applicable values had all these ids ...
0
votes
2answers
115 views

Joining ~ 40 normalized tables for 200 columns to display detailed user profile. Can this be optimized?

A profile can have many profile types. That profile can have many fields, which are all tied to lookup tables. Some fields can have many selections. profile -> profile_date -> one relationship ...
0
votes
0answers
24 views

MongoDB: Approach for storing frequently modified references

Need a piece of advice for storing a large number of references between Mongo entities that are frequently updated. Some background: 1 event can have 5000 logs. 1 event can have 10 assets associated ...
0
votes
0answers
13 views

Using LTREE type when the final node has ~1000 descendants, which exist under other final nodes

Is it inappropriate to use LTREE type when the final node has ~1000 descendants, which exist under other final nodes? My data is essentially a directory tree, with the final level containing text ...
0
votes
0answers
11 views

Should I use a composite key of member #, VIN, and transaction #, or just have the primary key of transaction #?

I'm trying to come up with a data model, and I'm stuck with this one table. It's named Rental_History, and it tracks the rental history of vehicles for a car rental service. My issue is this: ...
4
votes
0answers
104 views

Database design for ~ 2.7B rows per year / 26.000 data points every five minutes

The current situation We're currently looking into a new product that will send device data back to us to interpret. These are the numbers we're looking at: Devices will most likely send data every ...
1
vote
1answer
67 views

Designing a table for large amount of rows. No relationships

I am looking to design a database that'll allow me to store references to files. The table is pretty simple: Id (GUID)(PK) Path (nvarchar(250)) IsPublic (bit) FileType (int) The files can be ...
0
votes
1answer
24 views

Database design for object attributes [duplicate]

Currently I am trying to create a database design that is long lasting. But I hit a point where I not know how to continue. I have a object table in my database. That tables stores one unique object (...
0
votes
1answer
19 views

Single collection for multiple keys

I built a little setup with a raspberry pi and a temperature and humidity sensor. Every 5 minutes it reads sensor values and sends them both + a timestamp and a custom ID (there are multiple pis, each ...
2
votes
0answers
61 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. ...
0
votes
2answers
63 views

What are the disadvantages of using surrogate keys? [closed]

I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value. I know some advantages, for ...
0
votes
0answers
18 views

Relationship constrains in EER

When designing a EER diagram, how do you state or show a specific relationship constraint between entities (or can this even be done). We have a relationship between two entities E and F, but for a ...
0
votes
1answer
16 views

Database for Orders/Inquiries with “custom” Product Data

At the moment i practice with SQL Databases and created a simple Python tool to manage Order and Inquiry with a simple GUI for testing. Now I have a question i didn't found a good solution for: ...
0
votes
0answers
17 views

Symfony Quiz Entity Design

Dear Helpful SE Users, In my quest to get more experience of Symfony I thought I'd write a quiz website which I can use at school (I know that moodle does this but it is a bit overkill for my ...
-1
votes
1answer
33 views

Duplicate primary key

I am new in database and ms access.I am developing a database of income tax return form submitted by several taxpayers. Where each tax payer has an unique ID called ETIN (electronic taxpayer ...
0
votes
1answer
60 views

Any way to represent simple AND/OR business logic in a database?

I'm designing an application to provide students access to modules once they have completed certain online courses. I have tables for: Students, Modules, Courses, and StudentCourseResults. Course ...
3
votes
1answer
72 views

Solving supertype-subtype relationship without sacrificing data consistency in a relational database

Trying to get better at designing databases, I noticed I'm always stuck trying to solve variations of the exact same problem. Here is an example using common requirements: An online store sells ...
3
votes
2answers
45 views

How to represent bike components and their relationship to each other?

So I hope my question here isn't too vague. I'm currently thinking about how to model a bike with all its parts and their relationships to each other within a database. I haven't chosen a specific ...
0
votes
0answers
51 views

Create a clustered primary key on partitioned table

I have a table that has been partitioned recently based on a datetime column. The same table had a primary key (clustered) on the ID column. During the partitioning of the table, the primary key is ...
0
votes
1answer
39 views

Database Design for Product Promotions

Assume I have a products table with the following columns: id (PK) category_id (FK) color_id (FK) material_id (FK) seller_id (FK) size_id (FK) name price Further assume that there could be 0 or more ...
1
vote
2answers
96 views

Mapping a 1:1:N ternary relationship to a relational schema

I have a question about mapping (conceptual) n-ary relationships to (logical) relations. Assume we have three strong entity types: Member, Equipment, and Time Slot, and a relationship named Reserves ...
1
vote
2answers
22 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') ...
0
votes
1answer
16 views

Code review database design for storing college assessments & enrolments

I'm desigining a database for a university testing tool – running tests in the cloud, very similar to a CI system. It needs to store users, courses, enrolments, assessments, submissions & results....