Questions tagged [database-design]

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

Filter by
Sorted by
Tagged with
3
votes
0answers
52 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 '...
1
vote
1answer
55 views

Database structure for a large amount of data with different types

Can I ask you for give me tips how to design efficient database to store large amounts of data with different types. I would like to use SQL Server 2016 as the database management system. I have to ...
1
vote
1answer
32 views

How to handle removing ASP.NET users where their UserID is tied to rows that should be maintained even after a user is deleted?

I have a website that uses ASP.NET membership and so there are multiple aspnet_... tables in our database. We are creating a service for the users that allow them to essentially create laboratory ...
-1
votes
1answer
89 views

ER diagram to Relational Schema (Many to Many relationship)

In this ER diagram, can anyone explain how we should convert the many to many relationships ('view', 'update' and 'diagnose') to Relational Schema and then SQL? What I think is that there shouldn't be ...
1
vote
0answers
55 views

How to design a database for production volume and loses control? [closed]

I must develop a database for inputting information of the produced volume (hourly), quality and technical issues during this production. As the input of the production volume is made manually, the ...
1
vote
1answer
54 views

Need help with a table relationships and over-all design

My design is a Netflix clone which has movies and shows just like Netflix. There is also a table called transcodes which will have information on a movie or episode video transcode. My problem is, I ...
0
votes
1answer
27 views

Invoices kept in document with user information in no sql database is recommended?

I have a application which has tenant details like { "name" : "", "Address" : "" , ... Bills :[ current : {"rent":"" ,"electricity":"" ,"date":""}, previous : [{"rent":"" ,"electricity":"...
1
vote
1answer
43 views

One user affects all other users

I'm completely new to working with databases (other than retrieving data from them as a front end developer.) and have an idea I was toying with. However, I'm struggling to find exactly what to Google ...
0
votes
1answer
41 views

How do I draw the erd to model this scenario?

Customers are billed for the cost for the activity itself and also any "extra" items which are used as part of an activity. So I have the customers entity (identified by customer id) and an item ...
0
votes
0answers
29 views

When do I create a separate entity when drawing conceptual models?

I have these 3 scenarios: Rooms located in a house are assigned a room number within that house - thus, for example, each house has a room number 1. Each room has a phone number etc... My guess is ...
0
votes
2answers
65 views

What is the difference between a foreign key and a primary foreign key?

I understand that you use foreign keys to relate rows from one table to the other and if this foreign key also becomes a primary key of that table then it is a primary foreign key. But, for example, ...
0
votes
1answer
380 views

What is a good unique identifier for customer and payment details for this tourism company?

Given a tourism company, Air T&T, has customers. When a customer checks in to an AirTnT facility (i.e. house-share), AirTnT records the customer's first and last names, address, date of birth, ...
1
vote
3answers
177 views

Estimate query speed for +1.5 billion rows mysql table

I'm working on audio fingerprinting problem where I need to query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to ...
1
vote
3answers
63 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
41 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
28 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
33 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
36 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
41 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
52 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
39 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
108 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
208 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
29 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
36 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
29 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
149 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
64 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
53 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
58 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
70 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
93 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 ...
0
votes
1answer
92 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
43 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
23 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
vote
0answers
36 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
146 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
29 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
17 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: ...
5
votes
0answers
123 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
29 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
23 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
82 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
169 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
19 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 ...