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
10 views
Table structure for possible duplicated data (link previews table)
I'm structing a table for link previews and it's pretty easy.
TABLE: LinkPreviews
+---------------------+--------+-----------------+--------------+
| URL | Title | Description | ...
0
votes
0answers
16 views
the difference between customer address table and address table
I was having a look at this data model to get more acknowledged for designing my own. Could someone kindly tell me what I need the Customer_Address table for? why don't we just settle with the ...
0
votes
0answers
3 views
migrating data pipeline to AWS Glue
I was trying AWS Glue to migrate our current data pipeline from python scripts to AWS Glue . I was able to setup a crawler to pull the schema for the different Postgres databases . However, I am ...
0
votes
1answer
34 views
Join to a Same Combination of Tables
I want to join a first table to same combination of joins: X,Y,Z. The first table can be different table types, but they all join on same guid column. Table A may have 8 columns of different data ...
0
votes
1answer
24 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 ...
0
votes
0answers
41 views
Help finding compromise between id and key referencing
I have the following tables for keeping a product with its attributes:
Attributes
id name
1 color
2 material
Attribute_values
id attribute_id value
1 1 ...
-1
votes
0answers
13 views
SQL Log File to Table Insert/Update
I have a emp table which contains three columns. Lets say EmpNo, EmpName and Age. Empno is a primary key column. I am getting data from logs or text file. If there is no particular Empno then row ...
0
votes
1answer
35 views
Vehicle Pickup time database design
The objective of this is to give workshops the ability to give their customers a choice of vehicle pick-up times and likewise customers having the ability to choose their preferred pick-up time.
I ...
0
votes
1answer
37 views
Create Copy of all rows where foreign key matches specified and then replace foreign key
I'm new to database administration (this is my first time) and have been tasked with creating a database with functionality that allows users to select a "Model" as a current state which can then be ...
0
votes
0answers
19 views
How to store skip lists on disk?
This question was previously asked here, but the accepted answer does not answer the question and as a new user, I can't do much about it.
How do you store a skip list on disk directly, instead of ...
1
vote
1answer
26 views
MySQL schema design - how to decide between an `enum` vs. a table join
In designing a MySQL schema, should I avoid using an enum type altogether, as I have been reading really strong, but favored opinions on this subject, such as:
Stackoverflow post on efficiency of ...
0
votes
0answers
39 views
Why do we need different type variations to just represent string type of data for bytewise storage? [on hold]
I am currently learning overall about databases. At one MongoDB webinar, it said:
Legacy types like char, varchar, text, clob all sorts of different
variations to just represent a string suggest ...
1
vote
0answers
30 views
Translation from (a) Entity-Relationship Diagram with Weak Entity Sets to (b) a Relational Design
Translate the following ER Diagram into the relational model:
Here we have that "driver" and "race" are normal entity sets, where their primary key is the underlined attribute ("dId" for "driver" and ...
1
vote
0answers
45 views
Double associative entity in a business environment involving Order Item Reviews?
Suppose you have Item (item_id, item_name) and Order (order_id, user_id, order_date) entities, with primary keys denoted in bold. Because an order can consist of multiple items, and an item can be ...
0
votes
0answers
48 views
Subscription and addon payment DB design [on hold]
Please find a high level overview of the table design for subscription as well as addon payments for an e-commerce website. I have omitted several columns and tables for brevity.
Table descriptions
...
0
votes
1answer
58 views
Is it good practice to create a separate table for views count?
I have created a blog table where I'm having a field called views_count but I have heard that updating the views_count field on each page view is strain. So I have created now a seperate table for ...
1
vote
0answers
12 views
Designing a message system
I want to create a messaging system but I'm stuck in the relationships between the sender/receiver of the message and the message itself.
This is a list of requirements for this system:
Allow both ...
-1
votes
2answers
82 views
Data Warehouse: Auto Generate Slowly Changing Dimension Code For Any Table with Dynamic Sql
Is there any code to take OLTP/Stage data and transform it into Data Warehouse Kimball Slowly Changing Dimension Type 2? The code should be dynamic, and should be able to utilize any table as input.
...
1
vote
0answers
40 views
User permission businness logic
This question will also be posted in the stackoverflow, since dba and programmers may have different opinions.
First of all sorry for the long question, but I wrote some extra code so that I can ...
-1
votes
1answer
20 views
Creating blog fresh tables from old tables [closed]
I am re-creating a database structure from old tables. New tables should be optimized and easy to understand.
In old database the developer used video table name for storing video blogs. However I ...
0
votes
0answers
39 views
Are there separate Object Oriented and Relational Databases in PostgreSQL?
I am new to ORDBMS and I want to know, can I have two types of databases in Postgres? - Object Oriented databases and Relational databases separately. Or there is only one kind of database which is ...
-1
votes
1answer
25 views
Storing User Information after Deletion
I am building a collaboration platform that focuses on being a knowledge base for teams and a big part of creating a knowledge base is storing historical information even when an employee has left. ...
-1
votes
0answers
29 views
Second normal form (2NF), partial dependencies and keys
There is a relation R(A,B,C,D), with candidate keys A,B,C, and primary key A,B.
If C→D, is that relation in 2NF? I just want to know if partial dependency means only on primary keys or can it be on ...
-6
votes
0answers
101 views
Why does this site consider it bad design to have segregated databases/tables in a multi-tenant environment? [closed]
I am asking this question because this site wont let me comment since I dont have a 50 reputation yet I do have 37 years of experience in relational databases, network databases, and hierarchic ...
0
votes
0answers
51 views
Kimball Modelling One Column Linking to Many Dimensions
We are conducting Quality inspections, Appraisals, and Ratings on furniture. Many different types of furniture exist, chairs (1), shelves (2), couches (3), table (4). The respective DimProductType is ...
4
votes
2answers
62 views
Should a composite primary key out of 3-4 attributes or a unique artificial ID should be used for the situation below?
For a small beginner project to start with databases and system development, I'm trying to develop a system that represents the classic case of an administration system for students and exams. This is ...
1
vote
0answers
26 views
2 sets of multivalued attributes
I have multivalue attribute which I need to store in 2 different tables. It represents qualificiations for certain jobs. One table is for workers profiles and another for company. Company table states ...
0
votes
0answers
24 views
ERD for Employees, Manager, supervisor and budget [closed]
Construct an ERD for a company based on the following Business Rules:
Employee belongs to exactly one department
Department has one or many employees
A Manager takes care one or many budgets in the ...
0
votes
2answers
58 views
Is the following database design good in the following table for the situation explained below [closed]
Someone has created this table with the following information:
There are more entries in the database table which I have omitted for brevity purpose.
My ultimate goal is to display the following in ...
-2
votes
1answer
33 views
Storing an Invoice in MYSQL using multiple of the same columns [closed]
Here is the table:
-Invoice
-INVOICE_ID
-BARCODE
-PRODUCT_NAME
-PRODUCT_DESC
-PROUDCT_PRICE
-TOTAL
-DATE
-TIME
I am wondering if i make an invoice like the following ...
2
votes
2answers
52 views
Is there an easy way to have a non-IDENTITY, auto incrementing column based on another column?
+----+-------------+-----------------+-------------+
| ID | ProductName | ProductCategory | ProductCode |
+----+-------------+-----------------+-------------+
| 1 | Item 1 | 1 | ...
1
vote
1answer
22 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
13 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 ...
0
votes
0answers
22 views
MongoDB database design schema for slack-like application [closed]
I have a question about the size and performance of a collection in MongoDB. We are building a slack-like app with workspaces and channels. Each workspace has multiple channels and multiple users.
...
0
votes
0answers
27 views
Database design for mostly similar but differing records?
I am dealing with 4 types of requests, and these share 70% similar fields but some have an additional 10-12 unique fields.
Common fields:
id, created, date, start_date, end_date, is_active, admin_id, ...
3
votes
2answers
139 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
60 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
66 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
46 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
25 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
53 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
21 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 ...
0
votes
0answers
29 views
Handling Categories and subcategories?
I'm trying to build a marketplace (no shipping, no online paying, just users post announcements. I'm going to give an example with Gearbest, from my understanding in this case:
That should result in ...
1
vote
1answer
32 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
42 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 ...
0
votes
0answers
20 views
Optimal way to store json
I would like to store JSON in a database.
I know that most DBs offer some sort of indexing in case of querying based on a value in JSON but I will only rarely need this.
What is the optimal way to ...
2
votes
1answer
37 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
votes
0answers
46 views
Does table size effect query performance? [duplicate]
I have two tables named table1 and table2, having a size of 9 GB and 200 MB respectively.
table1 has 200'000+ records and table2 has 700'000+ records.
I'm using the below query for both tables, BUT ...
0
votes
0answers
26 views
Database for product catalog with multiple stores, hierarchical categories [closed]
I'm seeking for assistance on the decision of the best database, be that relational one or not, as well as the best schema for the given task.
The idea is the following: there is an offline store ...
1
vote
2answers
24 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 | ...
