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
The results are in! See what nearly 90,000 developers picked as their most loved, dreaded, and desired coding languages and more in the 2019 Developer Survey.

Questions tagged [database-design]

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

0
votes
1answer
21 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) ...
0
votes
1answer
48 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, `...
-3
votes
0answers
30 views

need help to create an entity reltionship diagram (ERD)

I need to create an ERD for this following statement and only contain 3 tables. The statement is: an office supplier sells many products, each sourced from a different supplier. Here is the flt table ...
0
votes
1answer
26 views

Database Design for Cars

I'm designing a database to capture car data. Here's a simplified design for capturing year, make, model, and trim: This should handle data like this: 2017 Ford Mustang GT However, it turns out ...
1
vote
3answers
60 views

Database solution for a large matrix/very wide table

Suppose I have these two data frames (simplified for my question): users +---------+ | user_id | +---------+ | 1 | | 2 | | ... | +---------+ articles +------------+------------+ | ...
1
vote
1answer
510 views

Error in creating trigger

I have 4 tables: event: event_id(p.k) | uid | circle_id(f.k) activity: uid | performed_activity_id(f.k->event_id) | activity_type_id follow: follower_id | circle_id(f.k) notification: sender_id ...
-1
votes
0answers
18 views

Script to fix MySQL columns to work with strict-mode [on hold]

I'm trying to build a script to generate ALTER TABLE queries so that across whole schemas, columns are adjusted to be compatible with MySQL strict-mode.. I want it to be a simple as possible, making ...
0
votes
0answers
29 views

Parent/child table with include/exclude items

I have a table with parent-child relations. The relations can go n-level deep. There is also a table with elements that belong to a group. CREATE TABLE group_children( id serial PRIMARY KEY, ...
3
votes
2answers
4k views

Design database for real estate property management?

I have design this database diagram to real estate property management just I want to see if I get it right especially the relation between properties and finances because I made it one(property) to ...
7
votes
2answers
12k views

composite primary keys is a bad practice? [closed]

I want to know if composite primary keys is a bad practice and if not, on which scenarios is recommendable to use. My question is based on this article The part about composite primary keys: Bad ...
0
votes
0answers
17 views

does a fact table always need to have a field for aggregate?

When I was studying data warehouse modeling, the first thing I came into contact with was the order fact table, and the product, user, date dimension tables. The order table will have quantity, ...
0
votes
1answer
30 views

Combine many table into one table in mysql table design

I'm grappling to design table relation. Version is Mysql 5.6.10. My case is like below bill table, payment table, contract table, recruitment tables... are existing. Currently we're considering new ...
0
votes
1answer
19 views

How to store data in look-up tables when some of it may be semantically different, but logically the same?

I am working on a system that receives communications from various laboratories. Each laboratory has two separate, independently operated data services which means that Laboratory A and Laboratory B ...
0
votes
0answers
15 views

Looking for optimal DB design - game records

I'm designing a DB for an app, where people could save their game records and get some statistics. Game could be played 1x1 or 2x2 players. I expect ~20 active users per day, if that matters. I ...
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 ...
-2
votes
0answers
37 views

Codd's rules demonstration [on hold]

I am new to databases and I'm trying to understand Codd's rules. I have searched the web but I didn't manage to find any source to see a demonstration of Codd's rules. I am working on the database ...
0
votes
1answer
38 views

Avoiding a many-to-many relationship cycle between 3 tables

I'm designing a database for a IDLE CPU time donation system. and I struggled to design the database. The problem is the following. I have 3 entities (tables): donors: hold donors' information ...
0
votes
0answers
11 views

Conventions/Rules for the entity relationship modelling (ERM)

In an exam past paper I am asked: Use Entity Relationship modelling to identify relations, including attributes and indicating primary and foreign keys. The narrative is as follows: A Pizza joint ...
0
votes
0answers
21 views

Modelling and normalising a tourism activities database [on hold]

Business rules Screens and external schema samples Normalisation attempts Considerations and questions How can I find which attributes would be needed to identify the date an activitiy was '...
0
votes
2answers
29 views

Two composite foreign keys have identical columns which must be equal value

Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id) I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, ...
-6
votes
0answers
28 views

You are the owner of a video game rental store like a physical store version of GameFly [on hold]

You need to create a database to help you run your business. Define 4 tables that will be in your database
-3
votes
0answers
14 views

A Comparison between Storage Key and Integer Position! [on hold]

It is understood that a) column store can be emulated in a row store by converting each column into one physical table and introducing integer position in each table. b) In vertical store: ...
1
vote
3answers
757 views

How to design parent, child, photos relationships in mysql when photos can belong to either parent or child?

Let's say I have a parent table and a child table with parent_is as foreign key. Now I want to store photos that could either belong to parent or child. so I could have a parent_id and a child_id ...
0
votes
1answer
32 views

How to structure a nested database coming from JSON? [on hold]

I am fairly new to SQL-based databases and recently installed MySQL. This is my scenario: I have a data server which stores user account data. Furthermore, there are many subservices that all have ...
1
vote
0answers
24 views

Schema for managing house rentals

I'm desiging a database schema that manages house rentals Below is what I have so far property table CREATE TABLE public.property ( id bigint NOT NULL DEFAULT nextval('property_id_seq'::...
0
votes
1answer
86 views

How to maintain customer addresses for Orders?

The database I am designing is part of a project I am working on. I have come up with 3 simple "business rules": Customers must either be Individuals or Businesses (a business belongs to a certain ...
1
vote
2answers
134 views

Foreign key as part of composite primary key or not?

I'm designing a relational database. This is what I'm trying to design: I will have production orders and products. A production order will have one product, but a product could be on zero or more ...
0
votes
1answer
83 views

Is it good to save Keywords stored by users in a new DB schema?

My question is more from the schema design and of further performance of the DB. I'm designing a DB schema which will store primarily strings. Actually, users work on these string, as normal mortals, ...
12
votes
5answers
20k views

Survey database design: associate an answer to a user

I'm doing the conceptual model for a survey database. The goal is store the answers given by users (it's going to be an Android app). I have three entities: user, question and option. A question ...
3
votes
0answers
39 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
47 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
28 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 ...
0
votes
0answers
12 views

Restrict EDIT and DELETE actions on local database [on hold]

in my current project the user setups an application locally on his machine and interacts without any internet connection. All his actions in the application are stored in a database. The problem with ...
3
votes
1answer
13k views

Number of ISA relationship triangles in an Entity-Relationship Diagram

The following two diagrams are nearly identical. However, in one, there are two ISA triangles from the Entity "Movie" and in the other there is just one. What is the difference in meaning?
1
vote
0answers
33 views

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

I must develop a database for inputting information of the produced volume (hourly), quality and technical issues during this production. There are 3 production lines. Each line can run through ...
0
votes
1answer
552 views

Designing a database structure for a permissions system

Scenario I would now like to implement a permission system so that a user is able to restrict access on his contact properties to his connections, e.g.: A wants to allow B to access his name, ...
-1
votes
1answer
20 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 ...
0
votes
0answers
97 views

How do you model a recurring order in a shop?

I am making a B2B-shop system for a customer where their customers can order items from their shop. The system needs to handle placement of recurring orders like: "Every tuesday of the week send me 4 ...
1
vote
1answer
46 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
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
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
1answer
110 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 ...
0
votes
1answer
24 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
0answers
36 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 ...
-3
votes
0answers
25 views

3 Tier Architecture ASP - Get a record from MySQL Database [closed]

I have a table tblUser userName: Dylan userEmail: dylan@gmail.com I want to get the email address "dylan@gmail.com" in a textbox when I enter the name "Dylan" in another textbox, using 3 Tier ...
1
vote
1answer
42 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 ...
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. ...
0
votes
1answer
25 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
1answer
86 views

Create one-to-many to two fields in different table

I have a database where I am trying to figure out how to join a table that contains an object called ModelType. The calibration table contains two fields (EffModel and BkgModel) that are based on a ...
0
votes
2answers
42 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, ...