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
0answers
34 views

Its my class project about Normalization to 3NF. I need help in this project?

I just want to know the normalization I have done is correct or there any mistake? Please tell me. enter image description here
1
vote
0answers
31 views

Database pattern with two types of owners for an object. Like how users and groups can make posts in FB [duplicate]

Please help. There is a problem I have tried to resolve for some time now. I want to come up with a pattern where i have a table with advertisements on a web platform. These advertisements shall be ...
1
vote
1answer
24 views

Optimal schema for my JSON data?

I am requiring my students to keep a 3 day food log. I want to use that data to display on their personalize web dashboard and will be bringing that info in as a JSON. The primary data I'll display is ...
1
vote
1answer
45 views

Does adding a surrogate key get rid of an identifying relationship between two entities?

In the following example, we have a Comments table with a composite primary key (PK) CREATE TABLE Comments ( video_id INT NOT NULL, user_id INT NOT NULL, comment_dt DATETIME NOT NULL, PRIMARY ...
2
votes
4answers
96 views

Should I redesign my “events” table

My system needs to store an append only log of events. Currently I have a database table that stores all the relevant data in one table: CREATE TABLE `events` ( `event_id` VARCHAR(255) NOT ...
1
vote
1answer
28 views

Different categories of missings

Most database systems can represent missing values, typically as "Null" or something similar. But what if I want to represent different categories of missing data? For categorical data, this is ...
4
votes
2answers
126 views

Is saving disk space still considered an advantage of normalization?

I have read a while back that one of the advantages of normalization is to save disk space (since normalization minimizes redundancy), but is saving disk space still considered an advantage of ...
0
votes
2answers
39 views

Should I create new table for each User to store their product List?

I'm creating a Web based billing and inventory management system. I need to store the product details such as name, price, stock left and also want to store details of every bill for each user ...
-1
votes
1answer
46 views

How do columns with excessive NULL values hurt database performance?

I have a legacy system that store comments in the database. I created a new system to replace the legacy system, that also stores comments in the database. For reasons that it would take too long to ...
0
votes
0answers
26 views

Minimum number of relational tables needed for this er diagram

I am having difficulties in converting such complex er diagrams to relational tables . Can anyone please explain how to approach such designs
0
votes
1answer
41 views

What is the SQL Server cost, in terms of performance, of sharing physical disks?

I am an application user in Health Care. The application relies on communication with a large database, including image data and other large numeric arrays. We add approximately 15GB per day to the ...
0
votes
0answers
26 views

Master keyword table connecting to multiple table

We have one keyword table and different tables connecting to the keyword table as the interface table (e.g question_keyword, answer_keyword). We would like to create one master keyword table so when ...
1
vote
2answers
46 views

Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries

I am facing the following problem: I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country ...
0
votes
1answer
32 views

How to relate two tables of data

Hello, I have this simple diagram showing two tables, event table and guest table. I created an event which has id of 1 and there are 3 guests for event id of 1. If I want to reuse the same guests ...
1
vote
1answer
114 views

Splitting large database into smaller ones

Our system currently stores all customer (merchant) accounts in one "flat" MySQL (5.6) DB namespace. We would like to scale out better - we are considering breaking our data up based on the merchant ...
1
vote
1answer
36 views

Can you get multiple values from a table into one row?

I'm extremely green at database design and have been given a project to complete as part of an assignment. My question is, in regards to values from a table, in this instance, it relates to health ...
-1
votes
1answer
22 views

The fastest way to Copy Schema or Table in MySQL DB

I have a schema (size 500 GB) in my MySQL DB and I want to make a copy of this schema to another schema in the same DB. May I ask what is the fastest way of copying all the tables in that schema? (I ...
0
votes
2answers
65 views

Most performant design for each member to manage his/her own news list?

Say a news website has news categories such as Politics, Business, Society, Education, Health, Tech, Celebrity, Lifestyle, ... etc. The number of categories may be more than 20. New categories may be ...
1
vote
2answers
131 views

Why is data integrity not possible when using denormalization?

This tutorial says the following: For proponents of denormalization, the thinking is as follows: normalization creates more tables as you proceed towards higher normal forms, but more tables ...
0
votes
0answers
26 views

Specifying primary keys, and designing relationships for my data

I have this database I want to use as a backend for a teacher resource database. The columns in question are as follows: In this table, a class has one subject, one course number, and one section. ...
0
votes
2answers
59 views

First names database design

I have a large amount of data on users (about 8 million users). I want to mine this data to create a table with statistics about first names. It should also include as many variations of each name as ...
0
votes
1answer
29 views

Am I on the right track for a customer user login database design?

I am designing a back end database that will handle registration and login. I need to be able to deal with different account and authentication types. The main issue here, that is different from ...
0
votes
1answer
18 views

Need the name value of the enterprise to be the same as it was when it was registered and not the value it currently has

I will explain the problem with an example: I am designing a specific case of referential integrity in a table. In the logical design there are two tables, enterprise and document. We register the ...
-2
votes
1answer
33 views

Db design help for Mobile device db in mysql [closed]

I have a database with 5 tables: device_company device_series device_model device_type device_data device_data will contain the id of all the above tables. I am wondering whether this is the best ...
-1
votes
1answer
57 views

What are the advantages and disadvantages of using a embedded sql database? [closed]

What are the advantages and disadvantages of using an embedded SQL database instead of MySQL server? I am a web developer and I'm planning to develop an application for the shop, without investing my ...
1
vote
0answers
22 views

Db Design: Junction table or column

I have three tables like below. user company user_company -------- ------- ------------------ id(pk) id(pk) user_id(pk, fk) company_id(pk, fk) My ...
0
votes
2answers
51 views

Modelling two dependent many-to-many relationships

I have a user who may have 0 or more foo's and bar's. Here is a diagram: Each foo has a unique integer ord. If a user has a bar, then this implies that he has a foo with ord=1. A user may not have ...
-2
votes
1answer
41 views

Is it efficient to design chat/likes/relationship database in separate table per user? [closed]

The specs: there would be about 1 billion users there would be probably 1 million topics each topics has subtopics stored at the same level as topics user has many to many relationship with topics (...
2
votes
0answers
38 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 ...
0
votes
0answers
25 views

How do I force a selection of items to a specific user/database row?

I have a database that allows a company to register, then the user of the company can register individual "user" accounts. The company administrator (who sets up the company account) can log in with ...
0
votes
0answers
22 views

is there Better way to do this kind database schema design, mysql

P.S. Hopefully I am not posting in the wrong place. If I am, please let me know where to post. I am currently using laravel with query builder instead of eloquent for a small self project. I am ...
0
votes
3answers
94 views

Model multiple 1-m relationship to a single entity

I have an entity called Box. This box has a from property which could be from a variety of different entities. It could be from a user, office, import, storage and many more. So far I can think of ...
1
vote
1answer
25 views

Subset of Unordered Pairs of Distinct Primary Keys With Normalization

I am currently in the process of designing a database. I have one table as follows: CREATE TABLE Set ( id INT PRIMARY KEY AUTO_INCREMENT, -- other values ); Now I wish to create another ...
0
votes
2answers
56 views

A table with many null values VS many tables

Basically my level of DB knowledge enables me to create, select, delete and some simple things. When it comes to designing for performance, I have absolutely 0 knowledge. So my question here is let ...
2
votes
0answers
33 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 ...
0
votes
2answers
178 views

Postgres jsonb vs composite type performance differences

What considerations are involved in choosing between a jsonb column and a composite type column of the same structure? For example, consider a column like that used in the Postgres documentation: ...
1
vote
2answers
73 views

Poor SQL Server Performance With Nested “OR” Conditions In LEFT OUTER JOIN

I have a tricky bit of SQL that's giving me a bit of trouble. I'd like to introduce several new columns to a key table whose components may or may not be used based on user configuration (Dimension's ...
1
vote
1answer
80 views

Converting (a) an entity-relationship diagram about an airline booking scenario to (b) a relational schema

I am currently following notes on a certain "7 step strategy" to transform an ERD to a relational schema and was wondering some points about step 4, converting one-to-many (1:N) relationships. The ...
1
vote
0answers
27 views

Cardinality with specialization in enhanced entity-relationship diagrams

I'm currently taking an introduction course to databases and I've come across a problem not described in the textbook. I'm wondering about the use of cardinality with the use of specialization in ...
0
votes
0answers
37 views

Multiple JOINs of possible properties in a 'flower' arrangement - acceptable design?

I have a functionality that has multiple (7+) potential properties to be added via a central 'hub' table. In most cases there will only be one property assigned, but it's possible the user will want ...
0
votes
1answer
54 views

Ensuring unique value for group of rows in table with trigger and concurrent transactions

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
5
votes
0answers
81 views

Which data model/schema to apply to a timeseries datastore for data sources with varying fields

I'm asked to develop a data-storage for time-series data, yet despite significant research I'm unsure about the data model and storage technology to chose. About the data The source data which is ...
2
votes
2answers
55 views

Which database management system type to choose for price tracking? [closed]

I'm writing a price tracking application. Every day I need to store price information about ~15M products. (So it's about 15M * 365 = 5.5B records per year). One product has many prices over ...
0
votes
0answers
34 views

Need an efficient mysql database schema to store points/ratings

I have four main tables- 1.user(user_id, name,...) 2. post(post_id, description, ...) 3.comment(comment_id,user_id,comment,...) 4.reply(reply_id,user_id, reply,...) and 5.user_post(user_id,...
1
vote
1answer
58 views

SQL Database structure to store calculations

I am in the process of designing a database structure to store some calculation information. Information about the problem: Each "calculation" is a collection of scripts allowing to calculate value, ...
0
votes
0answers
37 views

[MongoDB]See Collections (and Files) created in a Shard on the Cluster

My structure: A Sharded Cluster with multiple Shards. Each Shard contains 1 Collection, which is pinned to it by tags. Every Shard is at the same time a ReplicaSet Secondary. It receives data from a ...
1
vote
0answers
16 views

Best practise for data dump 'entry point' in DB processing chain - PostgreSQL

I need to implement a process chain to transform and analyze potentially large sets of data, inserted both programmatically and manually (e.g. CSVs). In either way, the input data is standardized in ...
0
votes
1answer
25 views

Uncertainty about number format in MySQL

In Oracle Database, fields like this: example number(6,4) not null; Indicate that I'm reserving 6 digits for this column, 4 that are being used from the point to the right and 2 for the point ...
0
votes
0answers
20 views

Database Design to Store Events Which Have Different Required Attributes

I am building a billing database for my office. I am planning to write a set of queries to determine time overlaps because my users get billings per minute. In order to do this each user will have a ...
0
votes
1answer
49 views

Is it Possible to have a database table table of morethan 3000 fields [closed]

I have a form of more than 3000 fields and i want to create a table without splitting it into two. Is it okay? Or it will have problems?