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
votes
0answers
15 views

Database design for job board based on neighbouring cities

This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relationships for the data represented below Say i have a microjob board, users sees job ...
0
votes
0answers
5 views

Graph Database - data modelling - linking multiple pairs of nodes using the same edge?

I am trying understand the concepts, which apply to data modeling using Graphs - specifically on SQL Server 2019. One thing I am unsure of is can the same edge be used to connect different pairs of ...
0
votes
1answer
17 views

Design help / Sanity check - Work order system

I, a programmer, have been tasked with creating a work order system for my group. MariaDB is the database engine we think we should use. I think I've done enough reading over the last month to be ...
0
votes
1answer
35 views

Should a inherited table have a new surrogate key, or just use parent table's key?

I am modeling some things with an "is a" relationship (aka inheritance), and there were two ways to go about it in regards to the inherited table's primary key and foreign key pointing back to the ...
0
votes
1answer
16 views

How to update a yes/no field to yes when all related fields in another table are marked yes

I'm new to database stuff (read: database idiot) and I am using Access. I have a table (let's call it "projects") that has a list of projects and one the fields (let's call it "actions_complete") for ...
0
votes
1answer
23 views

Duplicated table for each website user

I have started working on a new database and found something that stands out as abnormal. I have a table that holds contacts similar to below contacts +----+-------------+-------------+--------------...
0
votes
1answer
26 views

What's an alternative solution to using a null value for a foreign key field?

I have a few tables in my database which contain a Foreign Key field linking to another table. The problem is, the value for that field isn't always going to be present, and so sometimes I might have ...
0
votes
2answers
27 views

Database design for a school

we're designing the database for a school. Just a couple of questions: do we create one (1) table for users or a table for each user type (parents, teachers, students, admins)? I'm thinking both ...
0
votes
0answers
14 views

Structure of a project management database

I will do my best to explain my problem as precisely as possible but please feel free to ask questions if anything is unclear. I want to create a DB where Projects are billed. In general there are 2 ...
0
votes
0answers
18 views

Linking Subtype table to another table in EERD

I made this Enhanced Entity relationship diagram for my database project, but there is a problem that I have been mulling over. Over here, it can be seen that MANAGER is under the supertype of ...
0
votes
0answers
17 views

What are the types of these functional dependencies?

I was wondering if anyone could help me solve this homework problem I have for my database management class. We were given this table: We were then given the following functional dependencies ...
0
votes
1answer
38 views

Inserting data into another table and getting it back into an array field in mysql

I have two tables Products table Products Table CREATE TABLE `products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, `description` varchar(255) NOT NULL, `created` ...
0
votes
0answers
5 views

Translations of names in profiles: [2 tables, one for names + one for profiles] or [one table for both, but with jsonb field for names]?

I am developing a profile module where company names could be translated (ie company may have the same name but in different languages, for example Eng/Rus for Microsoft/Майкрософт). Users may use a ...
0
votes
1answer
41 views

Designing an ad banner tracker

I'm creating an ad banner tracker for the ad banners on my website by using PHP. The tracker would track unique impressions, total impressions, unique clicks and total clicks of each banner. The ...
0
votes
1answer
27 views

Relational database field name design

Are there design patterns for naming table fields? For example, I've always used user (table)> email (field), but I see other well-used modes like user (table)> user_email (field). Are there any rules ...
-1
votes
1answer
31 views

Need to understand the security flaw?

I am creating a database of encrypted value. Let us say I store "John" which would be encrypted and stored as "Yoky". John | Yoky Now I store "Johnny" which would be encrypted and store as "Koaddy" ...
0
votes
0answers
11 views

How to generate queries for Star Schema Benchmark SSB?

After generating 1 Gigabit of the Star Schema Benchmark available via https://github.com/Kyligence/ssb-kylin How to generate for exemple 1000 sql query to execute them on postgreSQL and sql server ...
-3
votes
1answer
43 views

From a backup strategy perspective, would it be efficient to use a relational DB (as MySql) and a document oriented DB (as MongoDB) in the same app?

I am developing an app where I need to store an undetermined number of properties for any given entity, for example: Entity || # of properties** Apples || 14 Oranges || 4 Pears |...
12
votes
4answers
2k views

What is the right data type to store only -1 0 and 1 in postgres column?

I want to store only 3 states in a column (if it was 2, I would use a boolean) I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space Is there a better way to ...
-6
votes
0answers
28 views

Whar is db user and db password? [closed]

I find root as username many a times. Is it a global user? How to create a user for sql ?
3
votes
2answers
72 views

How to store values that include numbers and strings? Example: 1.25, trace, not applicable, etc

I created a database for nutritional table information. This database have values like carbohydrates, protein and fats of a food (there is a total of 28 fields). The problem is that there are special ...
-1
votes
1answer
29 views

Alternative for creating Database and inside Database having schemes like Oracle in MySQL

In oracle we have database inside database we have schemes and inside schemas we have tables. For Example consider the following scenario I have 2 databases like Database1 and Database2. In ...
-2
votes
0answers
15 views

MySql Random Query Lock [closed]

My Database is getting locked by random mysql queries. How to avoid this problem? What i have tried yet to solve the problem. Increased buffer size approximate 5 GB Query cache disabled Default lock ...
0
votes
2answers
87 views

Drop a 500 GB table, any consequences? [duplicate]

I need to drop a table that is 500 GB in size. It contains historical data that's not needed or used. There are no FK constraints. Any consequences of just dropping the table? I'm also using DB ...
-1
votes
1answer
22 views

How to map dynamic test result and reference range data in database?

I'm designing a database for pathology laboratory. I'm currently using this database design to implement the laboratory. I have to map medical test results and reference data in the database. ...
1
vote
3answers
49 views

How can I design a relational database with words that are all related to each other

I'm developing a simple language game about antonyms, synonyms and 'similar speaking' words. If you play in the 'Synonyms screen', the game will show you two synonyms and two unrelated words and you ...
-1
votes
1answer
20 views

Trouble designing mongodb 1-many collection/query that could grow without bounds

So in an application I'm currently working on, I'm having an issue designing a collection. I can't give away too much information about the application, but here is a similar (albeit not as realistic) ...
-1
votes
0answers
10 views

Data modeling for queries with multiple scopes

Context: I provide compensation based on products. These products have categories. For reporting purposes I want the user to be able to group products by selecting multiple categories, a single ...
0
votes
1answer
37 views

Database model / design best practice

I'm looking for a some guidance/advice on how to best model my data. Some background: I have location information that I'm trying to model. There are two categories of locations (company and non-...
0
votes
1answer
49 views

How to Design Name and Address with Multiple Addresses

I have a names and addresses table: Name Address AddressType ID CustID Sample: Bob Smith, 123 Main St, Corporate, 1, 1 Bob Smith, 123 Main St, Home, 2, 1 null, P.O. Box 123, Warehouse, 3, 1 ...
-1
votes
0answers
28 views

What do these ERD relationship arrows mean practically? (One to one, zero or one to one)

So let's say we have this situation: I know this means that foreign key fk1 can be assigned to only one primary key id2 and likewise (meaning there cannot be duplicates of fk1 values in table1 as far ...
0
votes
1answer
49 views

Can I show a relation - “Either in this table or in that ” in ER diagram?

I am making a database for handling leave applications in an academic institute. Given that there is hierarchy existent in such institutes, and I need to keep record of the people that were at these ...
1
vote
1answer
22 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 ...
-3
votes
1answer
28 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 ...
-1
votes
0answers
35 views

What to specify at Host Name/Address if I want remote Access to my PostgreSQL Database from a Home PC?

Let's say I did the following Things. Host OS: Windows 10 PostgreSQL: 12.0 Things done: Change listen_addresses to listen_addresses = '*' in the postgresql.conf Add host all all 0.0.0.0/0 md5 in ...
0
votes
1answer
20 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
19 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
19 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
47 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
49 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
16 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
29 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
37 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
42 views

e-Cinema Ticket Reservation ERD

Here is my entity-relationship diagram for an online ticket reservation system. Can someone give a review that improves this diagram?
2
votes
0answers
29 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
24 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
33 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 ...
3
votes
0answers
70 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
31 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
31 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 ...