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
15 views
Creating a relationship for a database model based on that model's date range
I have a database of Schedule objects in PSQL (schedule_id, user_id, date_range ( [date, date) ), and a few other fields not necessary for this discussion).
Each of these Schedule objects is ...
0
votes
0answers
6 views
DB Structure and Search with weighted ratings
Let's assume the following structure. For each pair of item and category we have a rating (between 0 and 10)
item_id | category_id | rating
--------+-------------+-------
1 | 1 | ...
0
votes
0answers
4 views
How to display duplicate records or data in my database using PHP code
How to display duplicate names in a record using PHP
Sample Table DB table
NAME MID NAME ADDRESS
David Kidfu y.com
David Kidfoe you1@yahoo.com
Mike Kidfu ...
0
votes
0answers
17 views
steps for write ahead log protocol
What are the steps for write ahead log protocol?
I know these are the steps, but I don't know the order they occur in.
a. Updated data is persisted on disk.
b. Data is updated in memory.
c. The ...
1
vote
0answers
21 views
Database Design for tracking user flow
I want to design a user tracking system between different tables/models. I want to track the user's progress. There is a Module table which is related to sections and sections are related to sub-...
1
vote
0answers
16 views
EER Diagram - subclasses which are neither disjoint nor fully overlapping
Suppose we are writing an enhanced entity-relationship diagram (EERD) for a company's database. This company offers two services A and B. If the two services were disjoint, then this might look ...
2
votes
0answers
19 views
How to handle normalizing the concept of a grid, and also plan for data versioning?
I have inherited a database that has a normalized structure, except for its data tables which are aggressively denormalized. Our system mostly renders grids into a browser, and the previous developer ...
0
votes
0answers
39 views
Designing a database about beverages and containers
I am working with a database for a business environment where a beverage can be served in a cup or in a tray (a tray can hold multiple cups).
Cups can have 'Tea' or 'Coffee'. Cups have color, trays ...
1
vote
0answers
23 views
Modelling a database for a shopping list - should I represent the scenario with a “redundant” relationship?
[I may provide some pictures later]
I'm facing this issue in modeling an entity-relationship diagram. I'll have a shopping list, where for each item the user inserts, it'll be associated with one ...
1
vote
2answers
24 views
How to implement a dynamic referral program?
Example: Currently, we will provide 100 credits each to both the referrer and referred.
Then in the future, we might want to implement a different referral program. Such as change credits to 200 ...
1
vote
1answer
33 views
Setting up a many-to-many relationship based off of two other many-to-many relationships
I have 3 main top level entities:
Project (PK: Id)
Owner (PK: Id)
Product (PK: Id)
Here are the rules:
A Project can have many Owners. An Owner can have many Projects.
A Project can have many ...
1
vote
0answers
15 views
Design table that solve a service payment or a package service payment?
I am learning and practicing some database designing and case study on Selling Services on Internet. In my requirement, user can buy a post service and add more extra option if they need. In fact, ...
1
vote
0answers
23 views
Database design for multiple units of meassurement and pricings for each item/product
Table Products
id_product| name | ....
1 | abcd | ....
---
Table Pricing
id_price | qty | price_type | price
1 | 20 | discount | 20
---
Table Unit
id_unit | ...
3
votes
1answer
34 views
Definition of indexing, datatype configuration, and DBMS for SHA3-256 hashes
I am designing a system within which I will need to be able to search a database using a file or string SHA3-256 hash.
For example the hash of test is
...
0
votes
0answers
30 views
Reducing the number of tables used - mySQL
I have a table for tax implementation in my application, but I am not sure the design I have done for the table. Can someone review it?
val_taxes is the table which has all types of tax listed in ...
1
vote
1answer
17 views
How to specify the domain of an attribute in an entity-relationship diagram (ERD)?
In an ERD (as in, rectangles for entity types, diamonds for relationship types, ovals for attributes, etc.) how would one specify that an attribute is, say, a 5-letter word?
-1
votes
2answers
29 views
Table Structure for Storing multiple foreign keys
I have two tables says, val_taxes and val_tax_groups the first table has all the tax separately like so
val_taxes
| id | tax_name | tax_value |
-----------------------------
| 1 | sgst | 2.4 ...
0
votes
1answer
36 views
Database design - repeating values
I have an application that deals with businesses and their physical locations. So most tables have a location_id field that logically partitions the data.
One table deals with marketing referrals and ...
0
votes
0answers
28 views
How to Represent a VARCHAR Column with XML Data in a Fact Table?
I have SQL Server 2016 environment and I have decided to design a source table with holds some sort of event data as a transactional fact table. The table has about a million rows, and generates about ...
-3
votes
0answers
21 views
Database with support for column append [closed]
I am looking for a database solution that supports column append out of the box. Ideally, it should work like pandas library where new column can be added very easily in one command. Row values in the ...
-1
votes
0answers
19 views
Best Database for Linear Graphs [closed]
I was wondering what the best way to store linear data (x & y) in a database would be. The idea is to be able to store millions of these points, then retrieve them all at once in a fast and ...
0
votes
2answers
50 views
multiple ids table for accounting schema
I am trying to make a small accounting db and I have hit a dead end, my search bared no results as of how to make this idea work.
In the picture below you can see that I am trying to link multiple ...
0
votes
0answers
28 views
Need to know if my first database diagram is good [closed]
I read about database diagram and normalization, So I have managed to follow the normalization rules. But I am not sure if this is right or not ?
What my database should do:
I want to add posts ...
0
votes
1answer
61 views
Choosing a primary key for Cloud database [closed]
I'm trying to choose a data type for primary key in a relational database (MySQL 8) for a SaaS application in the Cloud. The problem I'm having is trusting the information I've found online. I've only ...
0
votes
0answers
21 views
Database design to store user relationships
My DB model is shown in the picture. Does this design look good? I welcome comments and ideas.
Description:
User role table keeps track of all possible roles a user can have
Role table has all ...
4
votes
1answer
75 views
Is it better to have a wider single table or “child” tables based on business processes?
The example I am working with is an invoice data model, where there may be around 30 columns by the time we're done designing. Each row of data would be unique to a single invoice.
Some of the ...
8
votes
2answers
640 views
Is a table without a primary key normalized?
In a lecture, my lecturer showed us a table without a primary key. Upon questioning, he said that in 3NF when you remove transitive dependencies it's ok to then have a table without a primary key.
...
0
votes
1answer
51 views
Appropriate column type(s) for dates and dynamic pricing data
What is the appropriate column(s) type(s) (trying to be database agnostic, but realize that could play into the answer) to store a months worth of pricing data (i.e. could be the daily hotel rate, etc....
5
votes
4answers
442 views
Which columns should be indexed when all may be used in different search queries?
Background
I'm working on a website for a movie theather chain currently located in four different cities (might expand in the future). They use the same, single-database website for all cities, ...
0
votes
0answers
15 views
Dance/martial arts school ERD asssessment
This is an ERD I've put together as the initial structure for a martial arts or dance type school. One person could have multiple schools, each school multiple students, etc. One schedule per school.
...
0
votes
0answers
29 views
Advice Regarding Database Design
I am working on a poory designed application, so please do not judge me by the work i have been posed. I have servers table whose data can be visualised as
and attributes table which can be ...
2
votes
1answer
60 views
Questioning a DB Consultant and their recommendations
We have hired a systems design consultant to help use architect a new CRM system, bringing together several business needs into one system, instead of the several we currently have.
It's the typical ...
0
votes
0answers
29 views
How to improve query performance through denormalization of this schema or other techniques?
My database attempts to describe some items, their sources and a variety of restrictions on who may use them as well as a number of intrinsic properties about the items.
It looks a little bit like:
...
0
votes
0answers
21 views
Syntax to establish a strong and weak entity relationship in MySQL? Weak entity's partial key is different from strong entity's primary key
The following image below depicts an entity-relationship diagram (ERD) of a strong and weak entity.
The closest example, I found here (link), required that a foreign key be defined in the weak entity,...
0
votes
0answers
17 views
Permissions and/or roles for Student Marks Database
I am creating a student marks database, but am wondering how best to handle permissions.
The current setup
Currently administrators, instructors, and students are all in their own tables. In the ...
1
vote
1answer
84 views
JSONB Array of Strings (with GIN index) versus Split Rows (B-Tree Index)
I have a database which stores receiver to indicate which account the data relates to. This has led to tons of duplication of data, as one set of data may create 3 separate rows, where all column data ...
0
votes
0answers
32 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
26 views
Database pattern with two types of owners for an object. Like how users and groups can make posts in FB [closed]
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
15 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
38 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
94 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
110 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
38 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
45 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
24 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
39 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
24 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
44 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
29 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 ...
