Questions tagged [database-design]
The development of the conceptual schema and/or the logical model and/or the physical settings of a database.
6,225
questions
1
vote
1
answer
881
views
Windows Azure Table Row Key strategy, what makes a useful RowKey?
I read somewhere that the row key should be useful - which is why auto numbers are not supported. However, for all 3 tables I've started saving I can't think of anything better to save in the row key ...
CommunityBot
- 1
4
votes
2
answers
975
views
Entity-Relationship Diagrams: Identifying which way a relationship flows
I am having trouble grasping the "relationship" concept of an entity-relationship diagram. For example, you have Classroom and Furniture as your entities. If you were to put a relationship diamond ...
CommunityBot
- 1
0
votes
1
answer
27
views
Modifying PostgreSQL Table for Handling Multiple Delivery Modes
I'm working on a project where I need to modify the PostgreSQL table structure to handle multiple delivery modes for an ordering system.
I have a table named seller with columns like id, user_id, ...
- 29.5k
1
vote
1
answer
907
views
Warehouse storage system (FIFO by BBD and date)
I have inventory table.
CREATE TABLE inventory
(
InventoryID int(11) unsigned PRIMARY KEY AUTO_INCREMENT,
InventoryDate datetime,
Quantity decimal(15,4),
BBD date NULL,
ItemID int(11) unsigned ...
CommunityBot
- 1
2
votes
1
answer
870
views
Drawing an entity-relationship diagram for a store business context
I have an entity called Users, with the attributes Id, Name, and Phone No. The objective is to develop an entity-relationship diagram (ERD for brevity) where Users might be Store Cashiers, and also ...
CommunityBot
- 1
0
votes
1
answer
41
views
How should I design my databas for online voting web app?
Currently I'm trying to build web app for online voting where registered user can create their own Election.
For the database, every time user create an election should the system create new required ...
- 1
1
vote
1
answer
1k
views
Finding all possible minimal covers
I have a relation schema R = {A, B, C} and the following functional dependencies:
A → B
A → C
B → A
B → C
C → A
C → B
How many different minimal covers can I derive from this relation schema? I have ...
CommunityBot
- 1
0
votes
0
answers
32
views
Balancing Legacy CRM Needs and Security in Multiple Databases
I have a set of databases on a server.
Database_alpha
Database_beta
Database_gamma
Database_delta
Database_alpha houses a legacy CRM system which has an inbuilt dependency on the sa account to ...
- 101
2
votes
1
answer
1k
views
Suggestion for a contact management database design
I am currently designing a contact management database for a chamber of commerce. The goal of the database is to store all the person (except our own staff), all recorded companies (regular companies ...
CommunityBot
- 1
5
votes
2
answers
16k
views
Financial database design
I am working on an application to store financial data and need to model the tables where the data for several financial statements will be stored. I came up with the following two designs but I can't ...
CommunityBot
- 1
2
votes
1
answer
848
views
How to model a database for prices that vary depending on their categories and parameters?
My first post on DBA! Apologies in advance if I made any mistake.
Before jumping into the schema and tables, I would like to share what I am trying to achieve first. I am working on sort of a courier ...
CommunityBot
- 1
1
vote
2
answers
2k
views
Database Design for Pokémon Review
I am trying to learn more about Relational Databases. So, I did a pokemon database gathering the stats available on the Smogon website. I have done some research thus far and would like to get some ...
CommunityBot
- 1
1
vote
1
answer
656
views
How can I arrange database structure for ranking card in a list in a Kanban Board?
thank you for helping me.
I got a problem when I design my database structure for kanban board application.
How can I design kanban board to rank card in a list for sorting,drag and drop,..
Currently ...
CommunityBot
- 1
0
votes
1
answer
3k
views
Database Design for Product Promotions
Assume I have a products table with the following columns:
id (PK)
category_id (FK)
color_id (FK)
material_id (FK)
seller_id (FK)
size_id (FK)
name
price
Further assume that there could be 0 or more ...
CommunityBot
- 1
0
votes
2
answers
652
views
Should we have OrderId and separate OrderNumber for E-Commerce Website or we can have only field for both?
I am working on an Online Grocery Store type of website in which I have one order table to store the order details. Here I am having confusion that whether I should have an OrderId (primary key) and ...
CommunityBot
- 1
-4
votes
0
answers
25
views
database 7777777 [closed]
A book is identified by its ISBN number, and it has a title, a price, and a
date of publication. It is published by a publisher, each of which has its
own ID number and a name. Each book has exactly ...
13
votes
1
answer
16k
views
Conceptual data model for a billing system
I am working on an existing MySQL database. My task is to create a new data model for a billing system on that existing application.
I have created a conceptual data model (click to enlarge):
##...
CommunityBot
- 1
1
vote
1
answer
336
views
How to handle a table schema with a new relationship subtype in an interviews scenario?
I have a table of "interviews" and "interview reviews" for both the interviewer and the interviewee. When the project was started there was only the need for one type of review, which is shown below
...
CommunityBot
- 1
0
votes
1
answer
2k
views
Understanding a double entry accounting DB Schema
This question is related to a previous question "Why the Accounting DB Schemas do not allow to maintain a clean Products Table?", asked on this network by someone else.
Assuming that
the ...
CommunityBot
- 1
36
votes
5
answers
8k
views
Why is there still a varchar data type?
Many of my databases have fields defined as varchars. This hasn't been much of a problem since I live and work in America (where the only language that exists is "American". ahem)
After ...
- 207
0
votes
2
answers
2k
views
Confusion over using LEFT JOIN on multiple tables
I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research.
Let's say we have a users table and other tables that relate to the user, let's ...
CommunityBot
- 1
1
vote
1
answer
64
views
Database design, one to one relation split into multiple tables
Let's assume we have following database:
Or in pseudo sql format (all columns are not null):
create table road (
id int PK
)
create table item_on_road (
id int PK
road_id int FK road.id
...
- 754
0
votes
1
answer
562
views
How to implement database schema for tracking the status of a purchase?
It's an internet stop, physical products with delivery.
I'm trying to figure out how to implement transactions/payments, fulfilment, shipping, payments, refunds, etc.
Namely, a status of each sale.
...
CommunityBot
- 1
0
votes
1
answer
148
views
How can I simplify the process of importing only changed data?
Background
Two years ago, I asked a question about how to model a food's relationship with its nutrients. Today, that design has evolved. The biggest change is that whenever a food is updated, we ...
- 53
2
votes
1
answer
67
views
Enforce unique constraint without denormalization
Option 1
I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD
A Task belongs to a Service (like in ECS)
A Task can be reassigned to another Service.
TaskConfig tracks ...
- 47.8k
-3
votes
1
answer
57
views
How to design database system of an online toy shop?
Hi i'm designing a database for a toy shop this shop offer many products with different categories. This is the project question. Can you please help me which tables and relationships should I add in ...
- 418
0
votes
1
answer
33
views
MySQL and PostgreSQL database table schema comparison
Is there any direct method to compare tables between MySQL and PostgreSQL.Comparing should include field name, types, default values, etc; As an example below,
Below is a dump of PostgreSQL
...
- 35.1k
1
vote
2
answers
34
views
Database Design For Authorization Of An Relation
Maybe the title of the question is wrong but I could not find a different way to put it. I am trying to create a term project for my database class. Here is my diagram
Here you can see the teacher ...
- 11.7k
5
votes
3
answers
5k
views
Database Design - One table for different types of transactions
Considering a procurement system as an example. Would it make sense to have one single table for the following: Quote, Order, Invoice and Credit Memo?
There will be some details that are different. ...
- 1
0
votes
1
answer
546
views
Creating and dropping partition in PostgreSQL "on the fly"?
In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative ...
CommunityBot
- 1
80
votes
6
answers
75k
views
What is the best way to store an email address in PostgreSQL?
What would be the right datatype to store email addresses in PostgreSQL?
I can use varchar (or even text), but I wonder if there is a more specific data type for emails.
- 119
1
vote
1
answer
54
views
building this not-so-necessary relationship is a good approach?
I created a database for an app, where I have several networks, which have several nodes and elements and also several scenarios, all related to the networks. [see picture below]
My question is: since ...
- 47.8k
0
votes
2
answers
55
views
Correct (as in normalized) way to express a relationship between 3 tables with complex(?) constraints?
I'll be using Postgres for the examples, but feel free to show examples in other databases if needed.
The simplified schema:
create table factory (
id serial primary key,
detail text not null
);
...
- 25.1k
-1
votes
0
answers
38
views
what violates the first normalization rule
I was convinced that what violated the first normation rule was having a multivalued column.
id name friends
1 'john' 'Maycon, Gabriel'
That has multiple names in the same column.
Compound ...
- 754
1
vote
0
answers
9
views
Data model for web form with different field (question) types
I am currently working on a data model for an application which includes a form / questionnaire.
I have reached a point where I am not sure how to proceed: What is the best way to store "grid/...
- 17.3k
0
votes
0
answers
22
views
User types as foreign keys
Largely theoretical question or idea discussion
Lets say we have several simple dictionaries:
create table dic1(code integer primary key, description varchar(256));
create table dic2(code integer ...
- 645
0
votes
2
answers
2k
views
How to snapshot or version a relational database when data changes?
My system receives data feeds. Each data feed will end up creating inserts and/or updates to most tables in the (relational) database.
I need to capture the snapshot of what the entire database ...
CommunityBot
- 1
1
vote
2
answers
471
views
Database design for news feed
I have one table news_feed_card which contains the following columns:
news_feed_card
id serial
card_type int
published_at date
Each news_feed_card can only have one news post. The problem is that I ...
- 418
0
votes
1
answer
296
views
Oracle Single table with json vs set of tables using joins
I'm building a reporting solution. The data will be stored on Oracle database. I expect to get near to several billion data set since i have to keep data for 1 year period. When designing database ...
CommunityBot
- 1
2
votes
1
answer
1k
views
Database model with Users, Resources, Permissions
I am fairly new to the DB design. I need to handle what a user can do on a specific resource.
One of the main queries I need to drive is:
Get all the cars a user has write permission to.
I think ...
CommunityBot
- 1
5
votes
1
answer
7k
views
Column constraint based on values in another column
I have a PostgreSQL table phase_steps, with the following example rows:
phase_step_id|step_type|step_status|
-------------+---------+-----------+
1| RESEARCH| |
2| ...
- 172k
-1
votes
0
answers
23
views
Best way to set up tables for EOD stock database
Im a newbie with postgre, and the course I did focused on querying already existing databases, not creating a database from scratch.
Im aware that using csv tables might be the best way to go for ...
0
votes
2
answers
58
views
What is the best database design when storing documents of different structures for users?
The requirement is: Users should be able to define the structure of the document they want to store.
For example, a user can decide to store a bank statement with the following structure:
post_date (...
-1
votes
1
answer
28
views
OS Upgrade Path for DB and Application
Are there any best practices which define which Operating System Upgrade Path we should choose and any rational behind it?
Path 1: Upgrade Database OS then Upgrade Application OS.
Path 2: Upgrade ...
- 1,494
1
vote
1
answer
354
views
Database design/schema for position hierarchy approval process for leave management
How do I design a database so that when a lower employee (associate) requests a leave that needs to be approved by their superiors the request will find its way up to them?
Any employee can request a ...
CommunityBot
- 1
0
votes
1
answer
281
views
How to structure database in order to lookup and store post ID's in one PostgreSQL command?
I have a table with user info (each user has a unique SERIAL ID), a posts table (each post also has a unique SERIAL ID, and references the ID of the user who posts it), a following table with two ...
CommunityBot
- 1
0
votes
2
answers
425
views
Lead to Order Table design
I have a User table having following fields:
id, name, phone, email
and an Order table:
id, order_name, order_desc, user_id (fk to User)
and a Lead table:
id, lead_desc, user_id (fk to User), ...
CommunityBot
- 1
0
votes
0
answers
27
views
How to stablish a partitions & indexes stategy in Postgres for 100s of milliion rows
For the purpose of learning, I am playing with Postgress. In particular partitions and indexing.
The Tables
Let's say I have the following tables on my system:
webs
web_id --> BigInt
web_tld --> ...
- 51
0
votes
1
answer
41
views
Postgresql near real-time
Before asking the question I will update you on the situation I face.
I have physical tables in postgresql that are continuously updated by various external flows (.NET, Java, etc...), at the moment ...
- 35.1k
2
votes
1
answer
937
views
How to link a customer to a country when the city/state isn't known
We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the ...
CommunityBot
- 1