Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
5,251
questions
0
votes
0answers
2 views
Object with many-to-many relationships with multiple tables
Here's an example for a use-case that matches what I'm trying to better understand. Say I have 3 objects I need to deal with that are similar in many ways, such as invoice, PO, and receipt. They can ...
0
votes
0answers
5 views
Multistore data architecture
I need to build a multistore across some countries where end-users see various views where products have different prices and promotions, ..etc.
I'm not an expert with databases, so based on my ...
0
votes
1answer
19 views
what is difference between uniform extents and mixed extents in Database file storage structure?
I just went to some resources saying:
uniform extents are owned by single object and mixed extents can be shared by maximum 8 objects.
But I'm unable to grasp their insight meaning, like what is the ...
0
votes
0answers
20 views
Design partitions key format - DBMS-agnostic
I ran across this problem few times in the past and I think I get it on the surface, but I kind of still miss the big picture.
Why do some DBMS-s/DBMS engines require partition key to be string ...
0
votes
0answers
12 views
Social network DB Choice and Design Help? [closed]
I am building a social network and need help with two facets of the DB administration.
First, I need to choose whether I use an SQL db (Likely Postgresql) or a Graph DB (Neo4J). Although I know a ...
0
votes
0answers
20 views
Constructing relational model from ER
I'm given the following ER model.
It's unclear to me how I'd identify copies. For the image entity I think it's sufficient to take the 'ArtistName' and 'ImageName' as a combined primary key.
Another ...
0
votes
1answer
26 views
How do foreign keys affect partial and transitive dependencies (databases)
I'm struggling to work out how foreign keys can affect partial and transitive dependencies when normalising databases.
I have multiple tables, two of which are job offer and accepted job.
in job offer,...
0
votes
1answer
22 views
MariaDB Replication - Replicate only specific tables and views
Note: A backend developer here with little to no experience in setting up database servers and replication.
What needs to be done
Setup DB replication of an existing database with the following ...
-6
votes
0answers
24 views
This is an ER diagram of Sports Club Management System Of Cricket. Help me to make the schema diagram for this [closed]
This is The ER diagram. Need the Schema Diagram
1
vote
1answer
21 views
Make sure date of departure is different than date of arrival?
I have the following tables:
Flights (id_flight)
Airports (id_airport)
Departures_Arrivals (id_flight, id_airport), which is the Junction Table between Flights and Airports and it has the following ...
0
votes
0answers
13 views
Tracking database field [closed]
I use a database whose tables and fields are used in different programs (source codes). I am looking for a program that allows me to keep track of which application uses which field and how. Can you ...
-1
votes
1answer
35 views
Database ER diagram review. Is this a good database design?
I have a task to design an ER diagram and task is given as points below is the description of the problem:
A telecommunications company employs thousands of consultants.
Each consultant is assigned ...
-1
votes
0answers
16 views
What options are available for creating a relationship in an ERD displaying how users given permissions can affect other users?
For my ERD:
What would be the best way to create a relationship (between entities) where a User is given a role that is associated with a chatroom permitting a User to act (banning, kicking etc...) on ...
0
votes
0answers
42 views
Create column referencing multiple PK's from same table
I am trying to create an table, menu items, to use in my front end.
Menu items can have 1 or many submenu items.
So one column must be able to contain 1 or more ID's from other menu item records.
...
0
votes
1answer
29 views
Database Design for Employee Information
I would like to ask if I'm doing this right since it's my first time creating a database model.
Basically what I'm trying do build is an Employee Information model where:
An employee can have ...
-1
votes
0answers
7 views
How to import data from AWS data lake to PostgreSQL DB? [closed]
We got already existing product which pushed data to AWS data lake and currently we are manually using that that data for some external processing. Now we are creating a product with PostgreSQL DB. We ...
0
votes
1answer
23 views
Table normalisation and empty values
Here are two ways I can optimise mapping of a text field with several other attributes to question and answer. First approach is, creating a map table with both question_id and answer_id in which ...
1
vote
0answers
20 views
Database Design For Employee Business Hours
I have a simple employee database (I use mySql):
Employee (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(40) NOT NULL,
email_Id varchar(40) NOT NULL,
mobile_Number varchar(15) ...
-1
votes
0answers
20 views
Database for large table [closed]
I have a large database of 400 million lines (rows) and each row has 60 columns, it's about 800-1000 GB.
I am very new in DB, just need to decide which database should use, some people suggest NO ...
0
votes
0answers
30 views
Am I over normalizing?
Suppose a payments transaction system where the transactions can be between users on the platform (Customer-Customer, Customer-Merchant, Merchant-Merchant) and users AND the platform (Customer-...
3
votes
2answers
115 views
Store VarChar(Max) [Notes] Column on Same or Separate Table
Disclaimer
I had and idea for a table design a little while ago that made sense to me at the time. In recent history I have realized I have a habit of "over engineering" or spending too much time ...
-1
votes
0answers
27 views
Database Design for Virtual Wallet [closed]
I am designing a virtual wallet on a e-commerce website.
This system has the following financial features:
Customers will be able to have credit in their account and can charge their credit.
...
-1
votes
1answer
23 views
what would be the best way to store password hashes with regards to k-anonymity? [closed]
do i need to do anything special or can i just store them in a single field table? i feel like i'm missing some security concerns if i just store the sha-1 hashes in a db with a single field table.
...
0
votes
1answer
32 views
Feasible to have thousands of foreign data wrappers in Postgres?
We are creating enterprise-grade SaaS where we will have many 1,000s of customers. We are considering creating a database in the Postgres server for every customer to ensure enterprise-grade security/...
0
votes
1answer
18 views
Transaction isolation level/design for write-only ledger
This is a simplified example of a problem I've been working on. Say I have the following database schema:
Table: Deposits
+--------+------------+--------+---------+
| ID | Date | Amount | ...
0
votes
0answers
9 views
Polymorphic association in PostgreSQL [closed]
I have three entities video_guide text_guide and discussion that represent type of learning material available on the platform. A student can create topic-wise boards and add learning material to them....
0
votes
0answers
11 views
Are relationships between subtypes of the same supertype entity possible in EERD?
I have a supertype entity Person that has the subtype entities Employee and Passenger. Both Employee and Passenger have an EmergencyContact which has common characteristics with the Person entity. Can ...
1
vote
3answers
50 views
Putting foreign keys of several tables into the same column
I've never come across this situation, not sure how to go about it.
Let's say I have a table Portfolio. I also have two tables: Asset 1, and Asset 2. I need to create a bridge between Portfolio and ...
1
vote
1answer
16 views
When placing an order in a shop, should a shapshop be taken of the products in an order also?
...to prevent messing up with data updates, such as prices, titles, of the products that are placed in an order.
Namely, a customer buys 3 items: for $5, $10 and $33 and pays for them. All is well. ...
1
vote
1answer
45 views
Is it Okay for mysql to hit 59% without no query running in my site? [closed]
Please I need some little help from you guys.
I have a VPS I bought since 2018 and since then one of the most difficult thing i have been facing is high load on my SQL, but i guess that was due to my ...
0
votes
1answer
22 views
What is the correct way to present the disks to ASM?
Hello I would like to know which is the correct way ... or the most recommended way to present the disks to ASM, for example: I have 3 disks mapped to a server (rhel7) orasys, orafra, oradata, with dm-...
0
votes
0answers
10 views
Use generated IDs or meaningful fields in mapping table? (user, permission, user permission)
Given the below tables, what is best practice and why for defining mapping table? Should mapping table consist of user.id, permission.id, or say user.email + permission.permission? Are there any ...
0
votes
0answers
9 views
When should I reference the User record or User Profile record
I know it is a common paradigm to separate tables when building a User profile. For example, having a user table, and another table called user_profile with a foreign key to the user table.
My ...
0
votes
0answers
10 views
How to optimize storage of sales details with multiple products in each sale in Microsoft AccessDb
I have created an accdb with customers and products tables.
Now, I want to know how to store the sales order details in the Sales table, WHEN each sale has multiple products, each with different ...
-1
votes
1answer
57 views
How to decompose R(a,b) with non FDs?
Assume student table R(a,b,c) is R(Student#,Sname,Class)
Student#(PK) | Sname | Class(PK)
001 danny c1
002 angie c2
002 angie c3
003 philip c1
...
0
votes
1answer
30 views
How to join different tables with same propriety tables?
Which would be the best solution to join 4 different tables with X tables that has to be the same for the 4 tables?
Let's presume we have 2 tables MENU and PLATES each of that tables could be joined ...
2
votes
0answers
43 views
Is a three-way associative relationship correct in this case
So I have started modelling some entity relationships.
It goes as follows:
A Mandate represent a client. A row for a client (Mandate) will only appear once in the Mandate table.
Each Mandate can ...
3
votes
2answers
61 views
Referential Integrity - Indirect Foreign Key In “Depth”
Question
What is the best practice for ensuring a Key from a "Grand-Parent" or "Great Grand-Parent" table is maintained when a "Child" or "Grand-Child" table is created from multiple relationship ...
0
votes
1answer
29 views
Best way to model a string matrix (postgreSQL)
My goal is to make this as convenient as possible (i.e. least external tables lookups in application) and efficient (least redundant indexes)
I need to model data that, without any optimization, ...
0
votes
1answer
26 views
How many databases does a sybase instance have?
I'm a newbie to sybase and could not find the clear answers on Sybase tutorials/docs
How many databases can one Sybase instance have? Oracle has one
instance which corresponds to only one database, ...
0
votes
0answers
13 views
Single table with many null fields or two tables used with union? [duplicate]
I am designing database for my small project and I've hit a "good design practice" wall ;)
I need to add records that will have one of two structures, containing different set of information (10-20 ...
1
vote
0answers
13 views
Data Modeling Problem - Schema to store varying number of data points [closed]
I need a schema to store varying number of data points. There are different data types. Each data type can have varying number of data points. For example: Data Type 1 will have 3 data points, Data ...
2
votes
1answer
24 views
Creatin Products Database with Multiple Color Variations
I found this Q&A already, that describes my problem and a more or less satisfying answer:
Create products with color variants
Basically, I want to set up a database for products with different ...
0
votes
1answer
23 views
Postgres use multiple tablespaces in a single database
Is it possible to allocate tables directly to tablespaces within a single database/schema in postgres? Based on the docs, tablespace is a create database argument, but are there any workarounds?
0
votes
1answer
20 views
Database: Storing multiple Types in single table or multiple intermediate delta tables
Using Java and Oracle.
We need to update changes in Email, UserID of employee to third party.
Actual table is Employee and intermediate table we keep which we will use for comparison of changes ...
3
votes
1answer
405 views
How is data lake and polyglot persistence different?
I just came across a new technology called polyglot persistence. However, it sounds very much like a data lake. So, how are these two things different?
0
votes
1answer
45 views
Is it bad to use NULL in foreign key column to mean something (e.g. this is the default data)?
I have tried searching for good guidance on this already, but without much luck. Still, apologies in advance if this is duplicated elsewhere.
The Problem
In a nutshell, we pay external contractors ...
1
vote
1answer
24 views
Data Modeling Problem (Tasks / Timerecords / Users) [closed]
It's late here an I'm having troubles wrapping my head around it.
I have 3 tables: Tasks, Timerecords, Users
A task is assigned to a user and always has an "estimated duration".
Timerecords are ...
0
votes
0answers
24 views
Question about Ternary Entity Relationship Model
I can't wrap my head around these claims concerning this Ternary ER Model.
Unfortunately this is in German so here are the translations:
Prüfer = Examiner
prüft = examines
Fach = Subject
Datum = ...
0
votes
2answers
21 views
Can we prevent table from update directly but allow update using trigger from another table?
So, let's say I have 3 tables:
stocks table, it have columns: id, product_id, stock
sell table, it have columns: id, product_id, qty
buy table, it have columns: id, product_id, qty
What I want to do:...
