Stack Exchange Network

Stack Exchange network consists of 174 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
1answer
30 views

Error 1062: Duplicate entry 'mydb/regimes' for key 'name'

I am not an expert on databases but I'm modeling a database on mysqlworkbench 8.0.13 and when I try to run the script it breaks at this table(regimes). I can't figure out what KEY 'NAME' it is ...
1
vote
0answers
15 views

How to use validation rule within database with drop down menus.

Building a scheduling database using access 2013 for my company. I currently have it set up so that only employees that have a rating of 3 or more for the particular job show up when you use the drop ...
3
votes
2answers
141 views

How can I restructure a table of points with repeating lengthy text ids?

I have a dataset that looks like this: 4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,82,49.226215,9.23175,244,1531840519 4cd1c79bf6a87692@@@b6aa5eeb-8a83-433f-a0ea-6c72708abc1d,86,49.22584,...
1
vote
1answer
64 views

What are the advantages and inconveniences of generalization or specialization of SQL tables?

This is a "theoretical" question linked to quality of table's definition. I work on tires and, currently, I have a big table that, for each tire's label, contains a row with label's values. For more ...
2
votes
2answers
111 views

How to prevent deadlocks in many-to-many insert/update trigger function?

I'm having a problem with deadlocks on a many-to-many insert and am pretty far out of my league at this point. I have a tweet table that receives thousands of records per second. One of the columns ...
2
votes
1answer
47 views

What are the origins of the Identity Column? [closed]

Who were the first creators of the Identity column? What problem were those creators originally trying to solve? Is there a story behind it?
1
vote
1answer
29 views

Merge 2 columns and replace with specific output

The setup is a Debian 8 with Postgres 9.4 (64bit if that matters). Given following table I want to "search" for a combination of columns first and column sub and replace their output with another ...
2
votes
1answer
93 views

Moving the data between partitions in a table

There is one table with partition, where partition function and scheme is defined like below: CREATE PARTITION FUNCTION DateRangePF (CHAR(8)) AS RANGE RIGHT FOR VALUES ('20180101','20190101') ...
2
votes
0answers
23 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 ...
1
vote
1answer
34 views

Is there a particular name for the type of structure of this database?

I'm trying to set up a data structure for an internal database of skills within a company, but I'm not entirely sure what "model" this fits as I'm quite new to databases and data structuring in ...
0
votes
1answer
49 views

SQLServer Row level security - Enforce from within database

Have a multi-tenant app running on a single sql 2016 db, I have a RLS (row level security ) predicate on customer_id (which is unique for each tenant) and the column exists in all tables. CREATE ...
2
votes
1answer
40 views

DB design to avoid versioning inconsistencies between the application layer and the database layer

Usually there is a 1-1 mapping between an object in the application layer and a table in the database layer. This coupling leads to complexities whenever there is a database schema change and the ...
1
vote
2answers
25 views

Recommended Way to Store Logical Expressions

Say, I have the following SQL table: | ID | GameId | PlanId | MinAgeLimit | Amount | |:--------------|--------|-------------|-------:| | 1 | Cricket | P1 | 10 | 500 | | 2 | ...
1
vote
0answers
24 views

Is it correct/acceptable to have two FK pointing to the same table?

Is it correct/acceptable to have two FK pointing to the same table? In this business logic, a message can only be sent from one user to another. For example: CREATE TABLE user ( id INT NOT NULL ...
1
vote
1answer
51 views

Best practices for Feed restructuring in a social app database

I have a social app, where users can post to the school they chose on registration. Each post can have a hashtag and a school (all pointers, (hashtag is optional, school is not)). Users see posts ...
2
votes
0answers
34 views

MongoDB - Best practices for Feed restructuring in a social app database [duplicate]

I have a social app, where users can post to the school they chose on registration. Each post can have a hashtag and a school (all pointers, (hashtag is optional, school is not)). Users see posts ...
0
votes
1answer
24 views

Table design to support pending changes

I have a users table. Users can belong to a group. For them I have a group table, with id, title and description. Now, any changes in the group table must be approved by an admin. So, I need a (...
1
vote
0answers
118 views

Gaming shop database schema

I'm trying to design schema for my personal project that would be an online gaming shop. I would like to ask you guys for any suggestions/tips on how could I maybe improve my current draft. Concerns: ...
0
votes
1answer
35 views

Database schema tracking patient stats over time

I want to create a database which allows to record patient reading data points like: weight, height, pressure, etc., over time. I found a database schema like the one discussed in this Q & A ...
1
vote
1answer
28 views

Column that must be unique only for rows with matching foreign key

I'm trying to enforce a uniqueness constraint on a table and I'm not sure the best way to go about it. I have two tables: Parent and Child. Child.parent_id is a foreign key referencing Parent.id. I ...
3
votes
2answers
181 views

What is the industry term for superset of “metrics” and “dimensions”? [closed]

If I want to refer to measures/metrics and dimensions with a single term, what would be a good industry-accepted expression? Metrics is used commonly in UIs as well such as Google Analytics or ...
0
votes
1answer
76 views

Format for recording RGB color in MySQL [closed]

React client is sending color to my server with this format {"r":1 "g":8 "b":5 "a":2}, for instance. I'm not sure what is the best way to record color in MySQL. I don't want to use this data in ...
0
votes
1answer
43 views

How to create relationship metadata between tables without a foreign key check?

I use database metadata (primary keys, foreign keys, table names, etc), to generate code and some very complex queries. This metadata resides in theINFORMATION_SCHEMA. I am having problems with ...
1
vote
0answers
20 views

How to model product part selection with restrictive rules?

I need to model database for product configurator where the user first selects a product template and adds features into that template. Available features need to be validated so that only features ...
7
votes
2answers
221 views

SQL Staging Tables: Primary Key Clustered or Heap

We are taking legacy flat txt files and inserting them into stage tables with SSIS.The question arose whether table should have primary clustered key index or not. This is direct flat file import with ...
4
votes
0answers
65 views

Can the Chase test algorithm only modify attributes on the right side of a functional dependency?

I'm attempting to apply the Chase Algorithm to determine if a decomposition is a lossless join decomposition. Functional Dependencies = {A → C, B → D, C → D, DE → C, CE → A} Here is my initial ...
2
votes
1answer
140 views

BCNF decomposition does not prevent insert anomaly?

I am in the process of designing a schema for a simple polling app and have tried to normalize it with the decomposition algorithm from this lecture. Requirements A poll has a unique id (poll_id), a ...
0
votes
1answer
150 views

database design for accounting systems

How the records are updated in an accounting system i.e., what is the standard practice? do you keep all the records and do the calculations on the fly or you add and subtract in each time from a ...
4
votes
1answer
82 views

Postgres jsonb column or standard normalized table?

I'm implementing an app with a payment system, and I need to record transactions that are made through the app. Also I need to use some of the information about the transaction for rendering some KPIs....
1
vote
1answer
30 views

“Centralising” a “decentral” design

I have a web app using a database per client (around 120 and growing, 20k users in total). Each database has its own user table with user_id, username, password, etc. Meaning: if a user is member of ...
0
votes
1answer
46 views

What is the best way to check if a value in a table has changed when writing to a database?

Let me provide you with an example. Let's say I have an html_tag with two columns, tag_type and tag_value For example, let's say I have a record with the value "link" under thetag_type field and "...
0
votes
3answers
610 views

How to store multiple data in a cell inside MySQL?

I want to store multiple dates in a row. Each row has a unique id and each corresponding id can have multiple action dates. The number of dates may differ for each row. I have multiple ID's and each ...
1
vote
0answers
37 views

Should I Use Nulls/Blanks to Convey Meaning in a Related Column Representing a Category?

I'm designing a personal database that has a table which contains categories for seasonal events of a video game. In my design I have a related column from Events to Items (one to many [1:m] ...
0
votes
1answer
64 views

Designing MySQL Database For Two Different User Types (Staff and Client)?

I'm creating a web-app that will connect to a MySQL database. This app will have 2 users: a Staff and Client that can both log in from the same login page. Both Staff and Client share the following ...
0
votes
1answer
57 views

Should I use bigint or varchar(20) as primary key, performance-wise, for Postgres?

I have 18 character long IDs I get from an API, and I currently store them as varchar(20). They are used as primary and foreign keys. Is it better in my case, to use bigint instead of a string type, ...
1
vote
1answer
30 views

Best structure for MySQL table

I'm designing my table, but I have doubts: I will have two columns A and B that cannot have repetitions and, in the same row, can't both be null. I don't know if the best thing to do is to use a ...
8
votes
1answer
163 views

Database Design for Forecasting Review

I am trying to learn more about Relational Databases and I figured there is no better way to learn then to actually do something. I decided to make a personal attempt to look at Personal Budget ...
0
votes
1answer
24 views

Using a combination of block storage and object storage for serving photos. Good idea?

I have a real estate app and I need to create a database for listing photos. These photos will be actively accessed by users. The full-size photos are about 50-70KB in size. I will create ...
2
votes
0answers
34 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-...
0
votes
1answer
180 views

How to structure table in MySQL for group chat application? [closed]

I'm making a messaging application, and I need a way to organize and store messages. I was thinking something like a table that had all the group chat IDs and who was in which group chat, but MySQL ...
4
votes
1answer
223 views

Should the referencing table have primary key, auto increment, or index?

I'm trying to implement a list. Each list item will be associated with one or more tags. And each tag will be associated with one or more list items. I'm still pretty new to this field. Fortunately, ...
4
votes
2answers
60 views

How do you read a one-to-many relationship in both directions?

I'm having issues with reading this relationship. Let's take the Professor and Class entities in this example. If I read this from Professor to Class, I would say, "A Professor teaches one to many ...
1
vote
1answer
46 views

Why does ENCODE on BYTEA not seem to have any performance impact?

I am working on a scenario to store strings in hex format of fixed length 64 to database. Obviously the choices are BYTEA and CHAR(64). Initial thought was to enforce a valid hex string storing it ...
1
vote
0answers
25 views

How do you aggregate the delta of values between two arbitrary datetime columns?

Please look at the columns and types of a table called History: - id PRIMARY KEY - account_id STRING - total_spent FLOAT - date_created DATETIME Sample data for this table: (id, account_id, ...
1
vote
1answer
65 views

A question about foreign keys and many to many relationships

My question is mostly conceptual in nature. I have a database schema that involves a many-to-many relationship between a School table and Student table. This seems sensible to me, given that a school ...
2
votes
2answers
65 views

Insert/update data into relational database tables only after administrator approval - how to approach it?

I'm building a relational database that holds 21 tables, which is connected to my website application through PHP. The website has search functionality which allows users to search this database. ...
0
votes
0answers
104 views

Best design for this project - MongoDB many-to-many self reference

I'm new to MongoDB and I wanted to create a collection of users that can subscribe to other users (basically a self join). Which of the following way is better (if any) and why? 1: Store both the ...
1
vote
0answers
48 views

Allow Null Columns vs separate 1-to-many tables

Perhaps similar questions have been asked, but this is re having null columns. I am too inexperienced to determine what would be the sounder way to approach the design of this database. Is it better ...
3
votes
1answer
99 views

Database table for exchange rates

I want to create a table in my database to store the exchange rates for currencies so I don't have to make an api call every time. My table has the following structure: from_currency to_currency ...
0
votes
1answer
101 views

One big table vs few smaller

Please take note that the example below is just an example, my scenario is way more complex and the way i'm trying to model it really makes sense Let's say i'm creating a table for audit events in ...