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
17 views

Is my schema correct?

The system has 3 main entities: Staff Courses Students I will try to explain only the Student table, and I hope the rest of the data will be clear. [ Students ] student_id: Primary Key full_name: * ...
0
votes
1answer
20 views

How to design database for monitoring the time trends of article tags?

My purpose is to monitor which tags increase/decrease rapidly last week on different bulletin boards. Or which tag do new appear. The current table schema of articles is as follows: article_id e.g. ...
0
votes
1answer
23 views

Insert JSONL with two columns of comma separated fields into separate rows in mysql

I have json datas in the format as shown below. **the number of objects under 'images'(VIEWS, ROOMS, etc.) varied for each JSON object. Not all objects have VIEWS...POOL, it may only have ROOMS. &...
0
votes
0answers
19 views

Database Schema Review

I am working on a web app for an ESL institue. The app will be used only by the staff members (Not the students) to manage classes, payments, and student data. I have gathered all of the data from the ...
0
votes
0answers
20 views

Connecting dimension tables in a multi-fact star schema database warehouse?

I am new to data warehouse modelling so please bear with me. In database warehouse modelling, the star schema is typically a fact table with multiple dimensions connected directly to it. However, what ...
0
votes
2answers
58 views

DBA's - What did you do that helped you gain the most understanding in your role? [closed]

I'm hopeful this question isn't so subjective that it's closed, as I think it can be helpful for newer DBA's who want to get a better overall understanding of being a competent, knowledgable DBA who ...
0
votes
0answers
21 views

How to deal with big database mysql? [closed]

we have a system whose data is increasing rapidly almost 10000 rows on daily basis. Due to which system performance is decreasing by every day. There is an option to optimize database queries and do ...
0
votes
2answers
30 views

User has Areas, which have Buildings, 3 PK's or tables in between?

I'm building a database which has the following User Area Building A User has many areas, and a user can add buildings to his/her areas. I can up with the following idea but I feel like there's some ...
0
votes
0answers
15 views

PostgreSQL - enforce uniqueness and cardinality of relationships [closed]

I'm trying to design a secured schema to support a feature in an existing multi-tenant database. The existing database holds customers and child branches (many branches to one customer). I'm trying to ...
0
votes
1answer
41 views

How to organise and understand a terribly modelled database

I have recently joined a company where there are hundreds of schemas and thousands of tables. All the schemas we have are from other vendors who have allowed us access to their database. The entire ...
4
votes
2answers
374 views

In postgres is there any real drawback on using a json field over multiple tables and one to many relationships?

In our current application we are basically going to provide something called "translations" for each field. So a table would originally be like: CREATE TABLE organisation ( id SERIAL ...
0
votes
1answer
30 views

MYSQL - Data Model design for reminders

I need some help on reminder data model design with repeat. Admin can set reminder for any client. Like, a reminder for calling as client on so and so date, or mailing a client on so and so date. One ...
0
votes
0answers
21 views

Bookkeeping Database Design

I'm new to DB design and was wondering if the following design can be improved. I want to create a bookkeping database which handles these use cases: For a given customer record all the transactions ...
2
votes
1answer
31 views

How to model a course > section > lesson structure where lessons can belong to either sections or courses?

I have three entities: Course Section Lesson Each "course" is made up of several "lessons". The "lessons" inside a course, can either be categorized into "sections&...
0
votes
0answers
29 views

PostgreSQL database design concerns

I am having a hard time deciding what's the best approach for my database design (I am using PostgreSQL 12) We have customers, each customer has a fleet of vehicles, each vehicle is identified as ...
0
votes
2answers
25 views

Storing sports teams with multiple alternative names

I am designing a MySQL database to store some football match results and statistics in. The data for populating the DB will be scraped from multiple sources and the names of the teams will be slightly ...
0
votes
1answer
19 views

Linking foreign keys across multiple databases: direct, or using an intermediary table?

I want to make a part of my application reusable, and that warrants moving the corresponding tables into a separate database. So for the sake of an example, please consider the two imaginary databases ...
0
votes
1answer
56 views

Database design for one payment and monthly payments

I'm working on leasing shops system and here is my database for now: I have contracts these contracts have 2 types, lease for a duration (year) and the payment will be once, and the other type is ...
0
votes
1answer
23 views

What is the word that means a query predicate can use an index [duplicate]

I remember reading in a paper a word that means “query predicate can be evaluated using an index” but I can’t recall the word. For example if you had a table with a column called “name” and you had an ...
3
votes
1answer
33 views

Indexing options for Postgres for a table with billion records when you want to index only recent records

We have a table which has close to 1 billion records taking up to ~315 Gigs. This is just the size of the raw table without the size taken by any indexes. This table has a updated_at column which gets ...
0
votes
1answer
40 views

Does it make sense to define a new “Data type”? Is there a way to do so?

I'm basically new to SQL, and I was making my own project when this question came to my mind. I'm making a database that has an entity where an attribute would be a "number" and a "year&...
0
votes
2answers
30 views

I'm trying to create a database for a hotel company with multiple hotels. I'm stuck on how to reserve a room?

I'm using Xampp phpmyadmin for the first time... So I've got the basics of the database. Customers, hotels, rooms etc. The customer can chose a check-in and check-out date. An admin can class a date ...
-1
votes
0answers
7 views

onfiguring griddb but, I am getting error as sown in the attached image

I am configuring griddb but, I am getting error as sown in the attached image. PLEASE HELP
-1
votes
3answers
74 views

SQL query - avoiding use of joins

Consider the following SQL database: I have two questions that I'm struggling on. I'm not really sure where to start. Select the third largest salary from all the employees. No joins allowed. ...
0
votes
1answer
33 views

Saving daily data for each user - MySQL

I'm currently thinking about how to store data that the user might (or might not) change each day. Please note, I'm not a rdb expert and I'm still trying to learn everything. For example, the user may ...
-2
votes
1answer
84 views

What are the advantages and disadvantages of normalisation as a technique for database design? [closed]

It may be easier to answer this with a snapshot of some unorganised data.
0
votes
0answers
20 views

Sample.exe is NOT created under the <C_CLIENT_SRC_PATH>\bin\x64\Release folder

I am following https://github.com/griddb/c_client to install griddb under Quick start (Windows) subheading it is written. A sample.exe is created under the <C_CLIENT_SRC_PATH>\bin\x64\Release ...
1
vote
1answer
47 views

Where to start learning the IDEF1X method of model notation?

I recently started learning about data modeling. After seeing @MDCCL's diagrams and thoughtful approach here, I'm interested in learning more about IDEF1X. I don't want to pay $200+ for the ISO ...
0
votes
1answer
41 views

I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table

I need to create a process where anything older than six months we would write to an archive table and delete the records from the inventory table. Our inv table is huge btw lets use (x) as the number....
0
votes
1answer
24 views

Best way to arrange a hairdresser database based on their skills

I need some help in a smart way to organize a database for a hairdresser shop. So far, I understand that I need a table for the hairdressers containing usual data such as id, name, etc. I also think I ...
0
votes
1answer
20 views

MySQL: Does the default value checked by the `CHECK` constraint?

Say I have the following schema, would the default value https://sample.com be checked by the CHECK (Website LIKE "https://%")? CREATE TABLE Company ( AccountID varchar(25), Name ...
0
votes
1answer
31 views

MySQL: Is it best practice to check whether some column is NULL by `NOT ISNULL(column_name)`?

Given the following schema, my question is that since I have already added the NOT NULL to IsBanned, would I have to check whether it is not null using the syntax NOT ISNULL(IsBanned)? And what's the ...
-1
votes
1answer
31 views

MySQL: Should I add NOT NULL UNIQUE to the field for primary key?

Hi I'm completely new to MySQL, I'm creating a database and my question is that: Should I add NOT NULL UNIQUE to the field for primary key? For example I have: CREATE TABLE Account ( AccountID ...
0
votes
0answers
41 views

General question on building a database [closed]

Sorry if this question is too general or if it sounds dumb. I have no background in database, but am in need of some help. Here is my problem: I need to build a database for a series of stream flow ...
-1
votes
1answer
26 views

Are there any advantages to having SQL hosting separate from a website? Hosting my own servers or paying for a service? [closed]

Background I have been tinkering with an application I am writing. It is essentially a POS system that stores information about customers and inventory. Currently I keep all the data stored in CSVs ...
0
votes
0answers
12 views

Best database design for performing binary analysis

I have some binary data that I would like to do some statistical analysis on. Let's say I have a few thousand 32 bit values. I want to be able to efficiently do things such as calculate parity bits or ...
0
votes
0answers
14 views

MS Northwind Traders DB Inventory Transactions Entity relationship question

Trying to understand Northwind Traders' db schema but I cannot comprehend the relationship between: Inventory Transactions<--->Purchase Order details Inventory Transactions<--->Order ...
0
votes
1answer
24 views

What is the most performant way to group by and join high cardinality columns in column based table?

I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The ...
0
votes
1answer
41 views

How to break down MySQL big table into multiple to solve operational issue [closed]

This is my current table schema in MySQL we are not facing any performance issue in our application but there are operational issue . The size of this table is 7 TB (6TB data and 1 TB index ) and has ...
0
votes
2answers
83 views

Is this product table schema good or worth editing?

I haven’t created such structures before and decided to ask the experts. Is this a good structure? I am concerned about the following questions: I created the "product_size" table ...
1
vote
1answer
47 views

How do I debug Postgres' join strategy?

In the process of optimizing a Postgres query, I ran into a situation where two equivalent, simple queries execute in very different amounts of time. While I can't provide a reproducible example for ...
0
votes
0answers
39 views

Can a database have an unnamed row?

I came across a question asking whether it is possible to have unnamed rows and columns in a database. I assumed unnamed implied primary key. I'm not sure whether my assumption is correct. When I ...
1
vote
1answer
28 views

How is data of multi valued attribute say phone number of person relation is stored in table?

I have searched for the answers referencing my books as well as web but i could not find one. So here i am. I am not sure what this question is trying to ask so help if anyone could.
-1
votes
2answers
31 views

Can the derived attribute (eg, age ) be present in the same table with the attribute it is derived from ( DOB)? If no then where should it be present?

I have an assignment that requires me to use particular attributes. The query that I have is. If I where to derive the attribute age from the DOB field, can the I use age as a field in my table? If ...
0
votes
0answers
23 views

DB relationships with historical tables review

Idea is simple. User -> Projects -> Sprints -> Tasks, but I need to keep historical versions for them. It will fit to my requirements or there are some changes to be applied? - or different ...
1
vote
1answer
18 views

text unique to the grain should be stored in fact table or dimension table?

I am modeling a star schema for user reviews using the yelp data set. Each user review has a business dimension key, user dimension key and a bunch of data associated with the review. All of the ...
1
vote
1answer
25 views

Selecting data from a mapping table using a foreign key

I'm trying to make a simple Login sys where each user should be shown data from a list depending on their user ID Using MS SQL. After searching for how to do it best i came across Mapping (pretty much ...
0
votes
2answers
26 views

Need help designing database, Create a table with additional columns/fields in the table, or create a new table for each different type?

So the tables that I have are Certificates, Approvers, CertificatesApprovers, Categories, Approvees, Status, AdditionalFields, AdditionalFieldsValues. Many Certificates to Many Approvers relationship. ...
0
votes
1answer
25 views

Consolidate / sort / combine / merge data for dummies

I am looking for a non-scripting solution to combine data from 3 different tables (preferably using spreadsheet software like Microsoft Excel but a database would also work e.g. Microsoft Access). I ...
0
votes
0answers
39 views

Database ER diagram review. Is this a good database design ? [HELP]

This database For school management system. Please review and help me with your ideas.

1
2 3 4 5
114