Stack Exchange Network

Stack Exchange network consists of 175 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

perfect database for time series and finding equal value in array [on hold]

what the best fit database for this task : select by unix/epoch time, lower/upper (> or <) than given time (timeseries) database that support array, and find word inside the array query example : ...
0
votes
0answers
18 views

Purging of unreferenced data via unconditional DELETE, by leaning on foreign key constraints

I'm designing a database from which old data has to be purged regularly for legal reasons, and I'm trying to figure out the best way to organise the purging. Dealing with the main tables is trivial, ...
0
votes
1answer
25 views

How to link customers and suppliers in a database

Some customers can be suppliers at the same time, so: how to design the database so we don't have two tables having the same columns, and, at the same time, we can link a customer and a supplier ...
1
vote
0answers
30 views

Is a relation with two attributes always in Boyce-Codd Normal Form (BCNF)?

I went through some tutorials on BCNF and got that a relation with two attributes is always in BCNF. My question What if a relation with two attributes has no prime or non-prime attributes? That is,...
1
vote
2answers
39 views

Deduplication/normalization of string column on insert

I have a database of text tuples. Imagine e.g. full file paths + standard comments. Then you dump large file tree and generate comments for files. A file can have lots of comments, but their text ...
0
votes
1answer
43 views

How to store tax for a Product when tax rate for a Product can change and tax-exempted Products

Product id name price Tax id name rate Product_Tax Product.id Tax.id effective date expiry date Is this a good design? How to store Products that are tax-exempted? Also, how to handle the ...
0
votes
1answer
47 views

How to store tax in the database when tax consists of multiple taxes

We need to design Tax table for Billing System. Lets say we have a SGST (State Tax) and CGST(Central Tax) and Final Intra-State Tax = SGST + CGST Tax table id name rate Tax table id | name ...
0
votes
0answers
23 views

“syncing” rows to another table

I have a database: companies -> properties properties -> accounts accounts -> line_items An "account" represents a dual-entry accounting account (like Cash or Accounts Receivable) Each property ...
0
votes
1answer
38 views

Enforcing “one-to-one-or-more” vs. “one-to-zero-or-more”

Suppose that I have a mini-world consisting of three types of entities, A, B, and C, subject to the following constraints: each A is associated with 0 or more B's, and each B must be associated with ...
1
vote
0answers
15 views

How to create a correct entity-relationship diagram (ERD) about a video game profits scenario

I'm starting to build my application database, and I'm really new to the this world, so I have a lot of doubts about my ERD and if its entity types and relationships are ok. This is an application ...
0
votes
0answers
40 views

SQL employee Database [closed]

This is my attempt, though I have done it in Access: Please help me, I would like to design an SQL college employee database. In my database I would like to have the following tables: Employee ...
1
vote
2answers
43 views

How to structure an append-only database in PostgreSQL?

Preface We have been stuck for years using a monolithic program with terrible and inefficient database structure. Now we are finally moving away from it with our own program. The only area we are ...
0
votes
0answers
30 views

On Delete Set Null for one-to-one Relationship [closed]

I am not great with database designs and I am having issues with my current design. Basically, there is one table called Users which has properties like username, password, etc. which handles the ...
-1
votes
0answers
34 views

What steps do you follow in making an entity-relationship diagram (ERD)? What do you look for? [closed]

I need to make an ERD and would like some tips on how I can make it? Are there specific things that I need to look out for, or that have to be added?
-4
votes
0answers
21 views

How backend and database of a online multiplayer gaming website works [closed]

I learned that in the database usually we have tables and websites interact with databases to show on their templates. I want to know how it works with gaming website. What happens when someone opens ...
0
votes
1answer
28 views

One column link to others

I always search my problem with different search terms but the problem I am facing now I can't express this in search keyword. So instead I came here to ask directly from humans as machines were ...
1
vote
1answer
37 views

Random selection with a “complex” subset structure

This is my first question in the whole StackExchange world. I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question. In my ...
0
votes
0answers
27 views

Database Construction

I am building my first SQL database for work. I want to be able to run queries that show what stocks a particular client owns. I have a table for clients which has an identifier for each portfolio ...
1
vote
1answer
33 views

Inheritance - What's better: three “equal” tables or one with a type column?

I'm having an existencial doubt: I have three conceptual entity types on my logic: - distributors - business_groups - clients All of three are "companies", and will have the same columns. The ...
1
vote
2answers
66 views

SQL Server - Physical Table Design - Column Order - Null and Non Null Columns

We are in the process of building out a database for a new application. I got some feedback from the architect that he read somewhere there is a memory utilization benefit for having all of the non-...
0
votes
0answers
30 views

UPDATE Query vs Reference Table?

The postgres database in question receives monthly updates, in the form of new tables. I am trying to work out which concept is most efficient to perform the task which I will describe below. Table1 ...
1
vote
1answer
64 views

Reviewing the design of a database about animals of two distinct species

I am creating a small database using SQL Server Express 2017. There are two animals (Foxes and Elephants). The Foxes table will have the columns: Identifier, Species, BodyLength, and FurColour. The ...
1
vote
0answers
18 views

How to implement correctly from SQL to NoSQL (DynamoDB)?

I am just new in NoSQL and DynamoDB. I try to moving from using AWS RDS to DynamoDB. I have table user which can have more than one of work experiences and can have more than one of interest (table ...
0
votes
1answer
45 views

Trigger Access table on Insert

I am using Access 2016 and building an in-house HR app for my team. I have a Position Table, an Employee Table, a Position_Employee Table and a Staffing Table Position Table ------------- ...
0
votes
1answer
32 views

How to define and query in case of table with Mutually exclusive one-to-many relationships

Hi I'm the following situation. I have 5 tables Entity ------------ idEntity entityName ... ... idCountry_fkey idRegion_fkey idAggregate_fkey Country --------------- idCountry countryName Region ...
0
votes
1answer
48 views

How to model parent-child relationship correctly in multi-tenant, multi-schema Postgres database

I have a multi-tenant SaaS app where each tenant gets their own schema. When a user creates an account, I create a new schema and run a bunch of migrations to create tables under that schema. Then, ...
1
vote
0answers
23 views

How should I design my table(s) to represent a list of objects, some of which are components of other objects? [duplicate]

My initial concept of this manufacturing DB would have a table where all parts and materials used to make finished products are part of a table: Raw Material A Raw Material B...Z Widget A Widget B......
0
votes
0answers
23 views

Normalization with optional column in a scenario involving companies, modules, components and parts

I have tables like this: company (id, name) module (id, name, FK: company_id) component (id, name, FK: module_id, FK: company_id) part (id, name, FK: component_id, FK: company_id) A part can either ...
2
votes
2answers
22 views

How to design a database for search history with multiple facets?

I want to show users a history of their searches, which can often be complicated: any of these terms: [a,b,c] all of these terms: [d,e,f] not these colors: [h,i,j] Since these searches are done ...
0
votes
1answer
22 views

Best practices for storing multiple SQL tables per user (built from uploaded csv file?)

I have a few users who will be uploading a few types of predefined CSV files. I need to store these as sql tables to be able to run queries on this data for a particular user, however that means there'...
0
votes
0answers
35 views

How to deal with refrencing a foreign key to two tables?

I have an interesting situation where I am making a data base off someone else's design that has to be as rule complaint as possible. The issue I have is with the section shown above as it is a sub-...
1
vote
2answers
46 views

How to store time values (without date parts) in Oracle? [closed]

I have to decide how time values without date or timezone parts (like java.time.LocalTime) are stored in an Oracle database. My options are Oracle's DATE data type (and "ignoring" the date part) or ...
1
vote
0answers
55 views

Is there a name for this type of data structure?

I'm working with a database that has a structure that few of my colleagues have encountered. The database is a fairly standard Oracle SQL database, but it has a number of tables called xxx_param, ...
1
vote
2answers
70 views

Same column present on two or more foreign key constraints

A simple schema leading to my question is the following: CREATE TABLE A ( AID INT AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE A_COPY ( AID INT, CID INT, PRIMARY KEY (AID, CID), ...
0
votes
1answer
22 views

modeling advice for table which may have 2 father tables

I am developing a ERP for a small cooperative with special needs (that'y why they need a non-general solution). I'd like to model the following: A "receipt" can either be incoming or outgoing For ...
1
vote
0answers
31 views

Setting up an database for an electrical system [closed]

I am trying to set up a database to help working on an electrical system. However, I am not sure of what and how to set up the tables due to the complexity of the schematic diagram. Following is a ...
1
vote
1answer
105 views

Which table gets the FK? Is there a clear Parent-Child relationship here?

I have the following tables: Each ProductLine will encompass many Parts. For this reason, it initially seemed to me that the Part table is a child of the ProductLine table. However, a part is not ...
0
votes
0answers
35 views

Schema: Table with many rows vs single table with many columns

I'm torn between two different schemas and would love some of your thoughts. Essentially, we have a list of items, and each item can have a bunch of extra boolean attributes, currently about 15 ...
1
vote
0answers
49 views

DB model for EV charging station booking system

I am prototyping a system that will allow users to book and pay for a connection on a charging station for their EVs in a certain location. Business rules they can book a connection for as long as ...
-1
votes
0answers
25 views

Is atomicity of domains always required?

Suppose there exists a social media site that allows users to tag posts, and each post can have any number of arbitrary tags. Isn't it easier to retain all the tag values in one single column along ...
0
votes
0answers
16 views

Schema - Can a Decision inherit attributes from unlinked entities

Drawing an EER Schema for a consulting firm, a process involves a Performance Manager (PM) evaluating the work of a Consultant on a Project. The evaluation tracks the name of the consultant, PM, and ...
0
votes
1answer
33 views

Assign foreign keys only after populating database

I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between ...
1
vote
0answers
47 views

UNIQUE constraint encompassing all the columns of a table - is it bad practice?

Is it considered bad practice to have a UNIQUE constraint applying to all columns on a table, essentially enforcing unique rows? My use case is to store tokens from textual message from various ...
1
vote
1answer
69 views

SQL Server Database Diagram

I am using SSMS v18, I need to create an ER diagram from my database. As Microsoft removed diagram from new version of SSMS (v18) so now How can I get a diagram for my database??
0
votes
2answers
22 views

mongodb schema design advice

I have a users index which has fields like id, email, password, etc. A user can see all other users except the ones who had blocked him. I want to design a database schema for this problem. So I'm ...
0
votes
0answers
26 views

One table or two tables?

If one table, let's call it table_1, has a composite alternate key made up of field1 and version, is it considered denormalized data, since field1 is repeating itself in the table (multiple rows with ...
3
votes
3answers
87 views

Proper normalization for an Orders table--whether to split or no

Each order has a unique SalesOrderNumber. An order is made up of various PartID numbers, each with an associated OrderQuantity number. I have the following table currently in my model: I'm ...
0
votes
0answers
22 views

Blog database, and multiple categories

I have 3 tables. A table called blog_posts which has your traditional blog fields like content, image, .etc. and a table called blog_categories which stores a number of categories a blog can be ...
-1
votes
1answer
24 views

Design schema to handle different data types (avoiding EVA anti-pattern)

So I have a table that stores ML model metadata. I have a table that holds my model. I need to also include hyper parameter information (epochs, batch_size, weight_decay, classes etc.). I don't want ...
2
votes
1answer
36 views

Basic information splitting and table design in a database about people, accounts and transactions

I am having trouble deciding which design would be considered a better practice. Say, for example, I have two tables: TABLE Person pid int primary key name varchar age int bank_account varchar ...