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.

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
2answers
260 views

Modeling Optional Column

I've got an optional column (residential) in one of my tables that is giving me trouble. If a Licensee intends to work w/ residential customers, they need to take an additional course (...
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 ...
0
votes
1answer
148 views

designing user friendly relational databases

I want to create a database system that is user friendly with the right relationships. My system should be in such a way that we have a writer's table, orders table, and payments table such that I am ...
0
votes
1answer
23 views

Optional Relational Filters

Let's say I have the following design: Tables: video_games A list of all known video games video_game_modes A list of game modes for each video game. For example: Game = League of Legends Game ...
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 ...
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-...
0
votes
1answer
152 views

Query other database's INFORMATION_SCHEMA tables

I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as ...
5
votes
1answer
3k views

How do completely empty columns in a large table affect performance?

I have 400 million rows in a Postgres db, and the table has 18 columns: id serial NOT NULL, a integer, b integer, c integer, d smallint, e timestamp without time zone, f smallint, g timestamp without ...
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 ...
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 ...
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 ...
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 ...
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 ...
6
votes
2answers
703 views

Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money

In the business context of relevance, both members and organizations need to have an account for funds. Funds can be transferred from member to member, from member to organization, from organization ...
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 | ...
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, ...
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
173 views

Database Design for Maintaining Supplier Invoice

I am not sure whether it is right to ask such question here. However, It comes to my mind this would be the best place to start. My needs are quite simple. As a distributor, I want to maintain all ...
3
votes
1answer
173 views

Is the following table for a simple music database in 3NF? If not why?

Im new to databases and I'm struggling to get my head around third normal form (3NF). I have been given a csv file where each record has fields song, album, artist and numerous tags. I have put the ...
0
votes
1answer
198 views

How to find functional dependancies from simply looking at a relational schema?

If only given a schema (table header, with primary key indicated), how do I find functional dependencies? My problem is that functional dependencies are determined in terms of the data stored in the ...
1
vote
1answer
160 views

How to model state change timeline

I've got an app where users can place orders for recurring delivery of a particular service. The orders table has columns service_id and state, where service_id links to the service the user has ...
0
votes
1answer
158 views

Database schema for timesheet app?

I am very new to database managements as I am enrolled in my first class involving databases at university. For my project, I have to create an app that clocks employees in an out and keeps track of ...
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,...
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?
0
votes
1answer
153 views

Creating a flexible table containing prices and periodes

I'm creating a system of availability for rooms and with a calendar according to the days, with every day have it's own price. Room Type | 01 | 02 | 03 | 04 | 05 | ... | 31 | -------------...
15
votes
1answer
17k views

MySQL - varchar length and performance

Is declaring VARCHAR size make sense for performance? Is there any difference (in speed) between VARCHAR(50) and VARCHAR(255)? Or defining length is logic/design constraint?
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 ...
10
votes
2answers
8k views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
7
votes
1answer
2k views

Does creating a unique constraint on a Postgres column remove the need to index it?

Does creating a unique constraint on a Postgres column remove the need to index it? I expect that an index is automatically needed to maintain the constraint efficiently.
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
162 views

Designing a database with a single valued attribute related to multiple entities

Here a customer can submit an order for one or many products . so, there is a one to many relation between customer_order and customer_ordered_products entities. Inside customer_ordered_products a ...
-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 ...
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 ...
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 ...
12
votes
3answers
16k views

Is there a standard implementation of a Users Database?

I need to implement basic personalized user functionality for my website. Is there a standard structure for databases of this type? Like is it common practice to have all user info and data in a ...
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 ...
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 ...
-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 ...
1
vote
2answers
180 views

How to design a database for query builder project?

I have this project in which user can create queries using web based UI in which there will be list of columns and applicable operators. Later users can edit these queries, too. So I need to store ...
2
votes
1answer
167 views

Best Practice : Schema design for Large Number of rows with few columns

I have about a data set that each entry is about 750,000 rows under it with each row having four fields. Each entry is in its' own CSV file currently and I want to get it into a database. Currently I ...
1
vote
1answer
188 views

How to handle multiple values in Single table

I am working on Hotel Reservation System in Asp.net with Sql Server. I have room Table there and Reservation Table when user select room the Key of that room inserted into Reservation table as foreign ...
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
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 ...
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
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 ...