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
1
vote
1answer
24 views

How would I maintain referential integrity across a database where I use versioned records while keeping data in sync?

I am building a database that consists of a few tables that are used as data sources for a form our application generates. [form] [question] [responses] id id id name ...
-2
votes
1answer
41 views

I need to put the data from the diagram in 3NF [closed]

This is a simple list table of a company trying to keep track of employee assignment to company projects (in other words, not a database but a flat one table file). You will design a database for ...
0
votes
1answer
26 views

Survey Database Structure

I am building a database to feed into a PowerBI dashboard. The data is extracted as CSV (SurveyResults) from Survey Monkey which will be loading into the MSSQL database. The outcome i am after is ...
0
votes
1answer
22 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 ...
-1
votes
1answer
24 views

Is there a best practice design for handling different types of the same object which have different properties?

This may be a rather naive question and I am not even sure if I am asking it in the best way, so my apologies if not. I would like to know if there is a standard, or best way to handle cases where ...
-1
votes
1answer
62 views

What are the right ways to analyse and tune up innodb_thread_concurrency in mysql 5.7?

I'm in a plan to increase thread concurrency in my production MySQL server 5.7 but not sure, before what are the params/configurations that need to be measured and configured to increase the thread ...
-1
votes
1answer
56 views

Optimise query based on year month (multiple timezone)

Note: Though I am working on sql server, this solution might not need to tie to a specific database. Just need a general idea on how it could be done. So, I have been research and found out that it ...
1
vote
0answers
17 views

Modeling a system with optional components

I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on ...
0
votes
1answer
31 views

Delete comment - actually delete it, or create `deleted` column and update it?

I'm really not sure if this is the right community to ask it in, but it seemed like it fits here. But anyway, I'd like to know what's the usual approach of deleting a comment from database through ...
1
vote
0answers
38 views

Database Design Strategy Help

Thanks for reading this. I’ve completely overthought this so I’m hoping to get some feedback. I have a customer table (custID, statusID), a notes table (custID, noteID), an action table (custID, ...
2
votes
0answers
30 views

Separate tables vs nullable columns [duplicate]

I'm going back and forth on whether or not to have three tables, or one, with nullable columns. In a business sense, I have 'Accounts'. An account can be either a Bank Account, or a Third Party. But ...
0
votes
0answers
25 views

Designing table relationships help

I would like some input on the best way to design this database relationship. We have a project entity, which has two areas as related entities. In the UI the main entity is the project, and then we ...
-1
votes
1answer
40 views

Database design for 'pay at table' product (integrating with restaurant APIs)

I am doing a database project for Uni and am trying to figure out how a 'Pay at Table' app would work for restaurants. I Have found this example company that does this https://readytopay.com/ but i ...
4
votes
0answers
77 views

Are these tables in 3NF? [closed]

Update: I updated the question to be more specific and also updated the diagram. I am currently in my last semester, I did use SQL before then I left for MongoDB but the practice project requires to ...
2
votes
1answer
34 views

Tracker issue database suggestion [closed]

Good morning everyone, i made this database i would like to know your opinions usertable and projecttable have a many to many relationship , ProjectCreatedByUser have two FKs (userID, projectID) , ...
2
votes
2answers
39 views

Schema design/data model for multi-level parent-child where each resource also has a one-to many with a shared resource type

Not sure if this is the right stack exchange for asking this question. If I should ask this elsewhere let me know. Let's pretend that I have a system in which at the moment there are 3 resources that ...
5
votes
1answer
81 views

Does it make sense to put a unique constraint on a nullable soft-delete flag?

So we currently have the following: MYTABLE COLUMN: ID (INTEGER Primary key, auto-incrementer) COLUMN: START (DATE) COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY) COLUMN: DELETED (INTEGER) CHECK:...
0
votes
1answer
56 views

Large archive table with “unimportant” data, how to improve/redesign? [closed]

We have a SQL DB that collects analog and digital sensor data from several hundred devices. The archive table is quite simple, just a (datetime) timestamp, (int) stationId, (int) datatapointId and (...
0
votes
2answers
15 views

Table with two customer IDs that I want to relate to the entity

Let's say I have a transaction where Party1 and Party2 are associated, eg by a transaction - let's say Party1 sells something to Party2. I have a list of transaction IDs, eg transaction1 is Party1 ...
0
votes
0answers
40 views

Database accounting design [duplicate]

i'm trying to design the database for my accounting app i read a little about database and i came with this design i really dont know if my logic is well here in tbl_journal the date, amount, desc ...
0
votes
0answers
29 views

What is the advantage of attached database design?

I'm working on a database and need to make some changes to it. When I was studying the present situation I came across attached design. I would never have designed it this way myself, but since I'm ...
1
vote
1answer
32 views

Database Model for Various Type of Sensors

I am planning to build a database that will store sensor value for various type of sensors in a factory building. Is there any best practice on how to do this? - Option 1 : should I integrate all ...
1
vote
5answers
74 views

“History-based” database design approach?

A few years ago I've stumbled upon a database design approach, but I can't find any information on this for the love of my life. Its main feature was normalizing tables in a way where every "property"...
0
votes
1answer
36 views

How to model multiple types of contacts?

I'm not sure if this can be called a polymorphic association - what is needed is to for each Message to have a Sender Contact and a Recipient Contact. Both Sender Contact and Recipient Contact could ...
0
votes
1answer
52 views

Restructuring table - start with PK or not?

I am restructuring a bunch of tables in my database (in this case using a smaller data type for some columns - in most cases cutting those by ~50%, and they tend to be large columns). I've gone ...
2
votes
2answers
36 views

Is partial dependency possible In Data Base for Foreign key relationship

I have 3 tables Student, Teacher & User. Student: CREATE TABLE Student( id INT NOT NULL PRIMARY KEY,name VARCHAR(50) NOT NULL); INSERT INTO [dbo].[Student]([id],[name]) VALUES(4,'Ram'),(5,'Raman'...
0
votes
0answers
52 views

Postgresql 10: Ldap authentication failing

I configured the pg_hba.conf of Postgresql 10 with following host Tom Tom XXX.XX.XXX.XX/32 ldap ldapserver=XX.XX.XXX.XX ldap ldapbasedn="ou=People,dc=example,dc=com" ldapbinddn="cn=Tom,ou=People,...
0
votes
1answer
43 views

What does this diamond shape represent in this ER diagram?

And what is testlog in this diagram is it attribute on relationship. Thanks
0
votes
0answers
24 views

Database design: repetitive data that needs to be uniquely identified

Imagine a project: I want to build a service containing a comprehensive list of all the books issued in a country (or in the whole world). I have access to various books databases containing imperfect ...
0
votes
1answer
27 views

Need help to design ER diagram

I need to design an ER diagram for a centralized healthcare management system for my college mini-project. Firstly, I came up with the following ER diagram. ER-diagram-1 While designing the tables ...
0
votes
1answer
109 views

Normalization of the this relation just want to know what are the functional dependencies

Consider the Customer account database of the ABC big bank, which keeps data for customers and their orders. A database of only one relation is designed, as shown below: ABC( customerNo, ...
0
votes
0answers
27 views

Microservice - Catalog and inventory tracking on different tables

I'm currently exploring the microservice architecture and base on what I've read so far it's good to separate the catalog microservice/database to inventory and that's where I want to go. The ...
1
vote
1answer
74 views

“Diamond Shaped Relationship” What's the formal term?

Consider the following "Diamond-Shaped" entity relationship diagram: I'm trying to find some formal readings on why this is a bad design - but Googling "Diamond Shaped Table Relationship" only turns ...
0
votes
0answers
21 views

Designing Doctor-Pacient access permissions

Id like to have several admins which in this case are "doctors" which will have access to their patients, however, a patient might have more than one doctor who has access to their data. What is the ...
1
vote
1answer
31 views

composite index on bigints

I have a User Relations table, which basically stores all the one-directional relations of users. For example , User A follows user B ; User X blocks User Y User ids: the id of users are bigints. It'...
0
votes
1answer
237 views

Should I not use camelCase in my column names?

I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase. In my PostgreSQL database, I currently have all my columns named following a camelCase convention,...
0
votes
1answer
39 views

Functional dependencies (hopefully simple question) [closed]

Consider the simple problem of keeping track of Students: STUDENT( StudentNumber, StudentName, Dorm, RoomType, DormCost, Club, ClubCost, Sibiling, Nickname ) Would ...
0
votes
1answer
103 views

Postgres text column too large for an index - searching for IP address, so I don't think text search will work

I have a Postgres database with a column that has a large amount of text in it. There are potentially multiple IP addresses in the text, as well as a lot of other text. I need to do a query WHERE ...
1
vote
0answers
35 views

Database design - dogmatic view? [closed]

I'm an application developer (full stack, I'm not shy of SQL, actually prefer it to some ORM solutions). The database architect (that has no coding role) for the project I'm working on is adamant ...
2
votes
1answer
38 views

Should i use multiple databases

I have a situation, in which i need to design a database for some bakeries. I will need to design the same thing for many bakeries. For each bakery i will have for example, an Employee table, and a ...
0
votes
0answers
22 views

Strategies/Schema for Unique Constraint depending on Grandparent ID

I'm using Rails 4 with Postgres (newest version). Wondering about design theory and best practice. Here is my current schema: create table Company ( id int not null primary key ) create table ...
0
votes
1answer
90 views

How to optimally structure a table with sparse data

I am working on a project to create a series of tables that are easier to use and more intuitive for fairly naive SQL users. Because of the structure of our data, we are running into a trade-off ...
2
votes
1answer
65 views

Why is this relation not in Boyce-Codd Normal Form (BCNF)?

I have the relation (A,B,C,D,E). The relevant functional dependencies are: { E,B,C } → { D } { A,B } → { C } { E,B,D } → { C } { E,A,B } → { D } The candidate key is (E,A,B). I do not understand ...
1
vote
1answer
114 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 ...
0
votes
2answers
70 views

MySQL - Column value depends on values from two different tables

I have a MySQL database that contains these tables (* denotes primary key): TableA: ---------------------------------------------- | *id | time | price | special_price | ---------------------...
0
votes
1answer
26 views

Enforcing type correctness for foreign keys into a generic typed table

We currently have a single table which represents a bunch of equipment (of different types). Since the fields are pretty similar, they're stored in the same table. select * from equipment; id | name ...
1
vote
1answer
87 views

Do I need to update an index include columns every time I add a new column

I work on a team where we have millions of records of data, but not 100s of millions anything crazy. We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is ...
2
votes
2answers
83 views

1-to-exactly-n in SQL

SQL has well understood ways to do one-to-one, one-to-many, and many-to-many relationships. But what about one-to-n, where n is a known natural number that isn't expected to change. (For example, let'...
2
votes
0answers
38 views

Schema for large amount of machine's sensor data variables [duplicate]

I plan to do a database for more machines. Each machine will have multiple sensors mounted that store data like temperature, current, power, fault, manual mode and so on every minute. Each machine ...
8
votes
6answers
2k views

Does an excessive table violate normalization rules?

Here is an example of some tables that exist in a database where I work. The data isn't actually around schools, but the structure is identical. There are four tables: ** School ** School Id, School ...