Questions tagged [database-design]

The development of the conceptual schema and/or the logical model and/or the physical settings of a database.

Filter by
Sorted by
Tagged with
0
votes
0answers
28 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
41 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
68 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
69 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
23 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
55 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
33 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
55 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
24 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
24 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
28 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
25 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
39 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
0answers
56 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
75 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
24 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
33 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
116 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
40 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
53 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 ...
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
35 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
50 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
87 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
24 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
94 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
24 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
30 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
39 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 ...
0
votes
0answers
127 views

Store large amounts of text is Postgres

I have a large text corpus that I am storing in Postgres. A table articles has all of the metadata for each article, about 19 million rows. It's primary key is articles.doc_id. Another table (texts) ...
0
votes
1answer
35 views

Foreign keys to prevent “orphan” file rows in a database for a scenario involving projects, files and downloads

In a system that monitors a file mirror by regularly downloading a file, I want to store a history of the results. So I have three tables: ┌──────────┐ ┌──────────────────┐ ┌────────┐ │projects │ │...
0
votes
3answers
61 views

How to generate unique and sequential Id values

We have an entity called Task which has properties like id, vendor id, name. So I created the following table to represent it: CREATE TABLE `Task` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
2
votes
2answers
179 views

Should I split timestamp parts into separate columns?

I am building a PostgreSQL database and I have created a timestamp table, where the primary key is the timestamp itself (e.g. id: Fri Apr 13 2018 15:00:19). The database is supposed to be later ...
1
vote
1answer
123 views

Table structure for entity versioning

For an assignment I have to build an application that is able to store tests. Every test has to be validated by a a different user when created or updated. When it has been validated by an user, the ...
3
votes
2answers
68 views

Are there relations that cannot be normalized to second normal form (2NF)?

I came across this example that I can't normalize to 2NF, so I was wondering if relations that could not be normalized existed. Here is the example relation: R(A, B, C, D, E) with the functional ...
0
votes
1answer
39 views

Creating a database for a scenario involving students, teachers and courses

I am undertaking a project that involves a student management system, but I am struggling to normalise/create relationships between certain tables. Here is my current setup: Models Users(id, first ...
1
vote
1answer
29 views

Designing Calendar table - datatypes

I'm in the process of designing the Calendar table. I took some inspiration from Database design book by Louis Davidson. What I'm confused about are the data types. Example: MonthName varchar(10) ...
0
votes
0answers
46 views

Designing a database for a weekly time schedule - speed vs space

I need to "store" a weekly time schedule as compactly as possible. The schedules are in 15-minute increments. In a 24 hour period that would give me 96 time slots. Midnight is 00, 0600 is 24, 1800 ...
1
vote
1answer
84 views

How to specify column positions in insert statements generated by pg_dump?

I need to add a column to a specific position in a table. I'm doing this because I'm merging tables from two different databases: DB1.schema1.table1 has these columns: name_pk | date | type while ...
0
votes
1answer
45 views

dynamic or static database tables

I am creating a little mulitplayer game. The game creates a session where players can join in. Then the session has to store some data provided in the game. Should I create a new session table ...
1
vote
0answers
19 views

Database design for roles for members of organisations

I'm developing an IDP providing the same REST-api for a number of organisations. Each user is a member of at least one organisation and it is based on OAuth2 and OpenID Connect. Every user of every ...
0
votes
0answers
24 views

Invoice Master-Detail and Grand Total

The problem is: Should I keep the "Grand Total" column on Master table? Which one is better, in terms of usability/simplicity and performance, which one is the "best practice"? The invoices ...
2
votes
1answer
78 views

How to design a parent-child relationship that includes child priority/order

I am trying to design a table with a string column in SQLite3, I believe this is necessary to connect the tables parent to child, which represent entity types that are involved in a connection with a ...
0
votes
2answers
34 views

Is it worth duplicating a table for different types of an entity when one should be unique?

In my database, I have a CUSTOMER table. I'm migrating an OPPORTUNITIES table, which contains two different types of opportunity, each having identical columns (e.g. status, reason lost, probability ...
1
vote
0answers
49 views

Storing GTIN/EAN values with PostgreSQL. Which data type to use?

I am building a product database and I need to store GTIN/EAN (Global Trade Item Number/European Article Number) values. Probably except GTIN-14 as it does not seem to be used at retail sales? I read ...
0
votes
1answer
21 views

How do I design a database/query that queries users's answers to user tags

My current setup in PostgreSQL: CREATE TABLE "usertag" ( "id" INTEGER NOT NULL DEFAULT E'nextval(\'usertag_id_seq\'::regclass)', "questionTitle" VARCHAR(255) NULL DEFAULT NULL, "factTitle"...
0
votes
1answer
42 views

Can I design a table that will use some columns just in certain times?

I have a User with one or more saved Documents, but each Document has specific attributes in Brasil for example we have: RG(General Register) have the following attributes: A number Expedition Date ...
0
votes
0answers
17 views

mongodb auto index future collections, big json size, correct structure?

We are going to index some data in mongodb (probbably), from some api calls sending json data ~10MB size, splited data in dates. Since the data is large, I think it's better to split the result in ...