r/DatabaseHelp • u/NewOCLibraryReddit • Aug 01 '24
r/DatabaseHelp • u/[deleted] • Aug 11 '20
My friend is doing a DB course right now, and he needs help with whatever this is.
Puppy Palace works with TV and movie producers who need dogs that can perform special
tricks, such as headstands, somersaults, ladder climbs, and various dog-and-pony tricks.
Puppy Palace has about 16 dogs on its books, and a list of 50 tricks to choose from. Each
dog can perform one or more tricks, and many tricks can be performed by more than one
dog. When a dog learns a new trick, the trainer assigns a skill level. Some customers insist
on using a dog that score a 10, which is the highest skill level.
Some issues to consider:
• A trainer may teach one or more puppy tricks in a given day, but every puppy trick must
be trained by only one trainer.
• Each customer may book more than one puppy for a given performance, but each
performance may contain puppies that hold different skill level.
• Each puppy must be assigned a single skill level. More than one puppy may be
associated with a given skill level.
i. Create a wireframe of complete application of the above scenario. [3]
ii. Extract entities and attributes from the wireframe designed/developed in part (i)
and draw a physical E-R diagram with integrity constraints (Primary key, Foreign
key etc.), placing minimum and maximum cardinalities on the diagram. [2]
iii. You were required to implement the tables from part (i) using SQL command
CREATE TABLE and perform INSERT operations for record population in Oracle
19c. Maintain the logical sequence the tables populated to ensure referential
integrity is preserved. [3]
iv. Create a Sequence for Puppy, Trick and Customer table’s primary key and start
with 1. [1]
v. Write triggers that automatically inserts the primary key with a sequential
number (using the Sequence created in the part (ii)) when inserting a record in
the Puppy, Trick and Customer table. [2]
2
vi. Create a package that comprised of a Procedure that returns the puppies with
unique puppy trick for the customers who would like to book more than one
puppy for a given performance level and a Functions that takes a particular
puppy_trick as input and will return the number of puppies that are trained on
that particular trick. [3]
vii. Write a statement-level trigger that updates the Total in the
CustomerRequestHeader table with the total value of the order_item records
whenever an insert, update or delete event occurs on the
CustomerRequestDetail table. For any update error, raise an exception. [2]
viii. Write a trigger to log the details of frequent customers with booking day and
amount paid for the customers who booked the puppy more than 10 times a
month. Also create a log table with appropriate columns. [1.5]
ix. Write a trigger to log any changes to Puppy booking rates where the increase is
greater than 30%. Also create a log table with action_date, old_rates and
new_rates columns. [1.5]
x. Write a query to find duplicate rows in Customer table. [1]
xi. Create a new table with data of Puppies with multiple puppy tricks and booked
twice by the Customers? [1]
xii. Write a query to display the list of puppies which are not booked by any customer.
[1]
xiii. Delete the duplicate rows from the puppy tricks table. [1]
xiv. Display the name of 3 highest profit making puppies? [1]
xv. Create a database in MongoDB with collections and documents for the wireframe
designed/developed in part (i) [2]
xvi. Apply insert, update, delete and find operation on the database created in part
(xv).
r/DatabaseHelp • u/Elemeandor • Jul 01 '25
Is there such a thing as too many "Many to Many" relationships?
For context, I'm working on a web based bullet hell editor that will allow users to CRUD playable bullet hell mini games. I'm using a UI and some pre-made elements that users will be able to tinker with.
For a quick run down, levels are made up of the following:
Waves: Spawn any amount of enemies in any number of x,y coordinates on the canvas at a specific time.
Enemies: Can fire bullets from any number of emitters. Can spawn in multiple different waves.
Emitters: Can fire any type of bullet. Can be attached to multiple enemies
Bullets: Can be fired from any enemy.
I'm trying to conceive an approach to making a postgres table schema, and it seems like I'll need a number of junction tables to handle a variety of many to many relationships. But before I take the deep dive, I wanted to know if this would cause problems. Even thinking about a query for fetching all level info from the database sounds like a potential nightmare (select all waves associated with the level id. Then, for every wave, select every enemy in the wave. For every enemy, select every bullet) and I can't help but feel as though this is the wrong way to approach things. Especially since, by doing this, I think I'll wind up fetching redundant data.
Am I just being paranoid, or is there nothing wrong with structuring tables this way and writing queries for said table structure?
r/DatabaseHelp • u/Sprinkles-Accurate • Jan 08 '26
Need help with planning a db schema/structure
Hello everyone, I'm currently working on a project where local businesses can add their invoices to a dashboard, and the customers will automatically receive reminders/overdue notices by text message. Users can also change the frequency/interval between reminders (measured in days).
I'm a bit confused, as this is the first time I'm designing a db schema with more than one table.
This is what I've come up with so far:
Users:
id: uuid
name: str
email: str
Invoices:
id: uuid
user_id: uuid
client_name: str
amount_due: float
due_date: date
date_paid: date or null
reminder_frequency: intInvoices table will hold the invoices for all the users, and the user will be shown invoices based on if the invoices have the corresponding user_id
Is this a good way to structure the db? Just looking for advice or confirmation I'm on the right track
Hello everyone, I'm currently working on a project where local businesses can add their invoices to a dashboard, and the customers will automatically receive reminders/overdue notices by text message. Users can also change the frequency/interval between reminders (measured in days).I'm a bit confused, as this is the first time I'm designing a db schema with more than one table.This is what I've come up with so far:Users:
id: uuid
name: str
email: str
Invoices:
id: uuid
user_id: uuid
client_name: str
amount_due: float
due_date: date
date_paid: date or null
reminder_frequency: intInvoices table will hold the invoices for all the users, and the user will be shown invoices based on if the invoices have the corresponding user_id
Is this a good way to structure the db? Just looking for advice or confirmation I'm on the right track
r/DatabaseHelp • u/wisepidgeon • Mar 20 '18
Redesigning Database Layer with an eye on Data Analysis
Hi everyone.
This is a long post, so you guys are warned! :)
I am in a situation at work where there is the possibility for redesigning everything. I want to take this opportunity in order to step up in my career, and anyway at least learn as much as I can to grow professionally.
We have sensors installed on customers' premises that count people (for instance, installed on doors in shops). Every 5 minutes, these sensors send data to our server that is made like this: (sensor_id, timestamp, count_in). For instance, it could be (2018-03-10T12:00:00Z, 42, 10), meaning that in the 5 minutes interval 2018-03-10T11:55:00Z-2018-03-10T12:00:00Z, the sensor with id=42 counted 10 people.
The goal is to collect all of this data in order to then be able to show statistics and perform analysis: the typical scenario is to show how the number of people that enters a shop changes over time. For instance, we could want to see the number of people that entered a shop every day of a month, or every month of a year, for a single sensor, or for a group of sensors.
To do this we need to perform sums on the data. For instance, in a day we usually have 288 records (5 minutes intervals in 24 hours), so if we want to calculate the total number of people that entered a shop in a particular year, we would have to sum the data of 288 * 365=105120 records, and the number grows linearly with the number of sensors we want to sum together (if we want to sum the data from 4 different sensors, we need to sum 288*365*4=420480 records).
Of course we are not Google, but I think we are talking about a decent amount of data to be processed. At the moment, we have around 3500 sensors, which bring us to around 1000000 records produced everyday, which bring us to around 11 tps. This number should -hopefully- increase over time with more customers, or with updates to current customers (new sensors etc). Each 5 minutes data could be summarized into ~100 Bytes, which, under current conditions, means ~100 MB/day, and ~36 GByte/year.
CURRENT SOLUTION:
The current solution is a PostgreSQL database that models data as follows. Keep in mind that the choice of modeling the db this way was not really justified with tests/benchmarks of any kind, but was left to the "feeling" of the previous developer.
There is only one table that holds the data. Each row represents the data of a particular day and a particular sensor. Each row is identified by the couple (date, sensor_id), date being the day without the time. The row then holds a jsonb column which contains all the "minute" data, whose keys are the datetime. The row also holds an integer value which is the total sum of the counts of each detail in the jsonb. This value is updated as new minute data enters the system. So, for instance, there could be a row (2018-03-10, 42) that represents the data of the sensor with id=42 in the day 2018-03-10. The jsonb column of that row cointains all the data to the minute, and their keys are 2018-03-10THH:mm:ssZ.
This structure is useful when we want to obatin statistics with certain granularity. For instance, if I want to obtain the total count of a year for a sensor, I would have to sum only 365 record (one per day) instead of 105120 (365*288). This helps particulary if I want multiple sensors (like, total count of 4 sensors, and this happens), because that number increase linearly with the number of sensors I want.
I see two problems here:
"scalability": even in case of no more customers, we keep having data. The db is going to grow even if we add no more customers. Of course we are not talking about petabytes of data, but in the long run this could be an issue. And performing grouping and sums, and averages over big amount of data seems to be a bottleneck.
This structure needs us to compute and update the aggregated count values as data enters. This puts a lot of load on the server (we need to use strong transaction isolation). Also, sensors need to be assumed as unreliable, so our priority is to insert the data they send as fast as possible in order to avoid retransmissions.
SOLUTIONS I AM THINKING ABOUT
Replace the data table with a data table whose key is (
dateTIME, sensor_id) instead of (date, sensor_id). So, data would now be memorized directly per minute, without aggregation. This should speed up a lot the writing, but now , when I have to perform sums I need to read more data (for 1 year on one sensor I need288*365=105120instead of just365)I honestly don't see why I should stay with a relational DB for this kind of data, instead of migrating to a NoSQL db. I would like advices on this: if you think it could be a good idea, and if so what could be a solution.
If we go for the NoSQL solution, we would lose the classic aggregation function (SUM; AVG...) which are what we essentially need to perform our statistics. I don't exactly know how to replace them. I had a quick intro to spark and Scala a couple of year ago, maybe that could be a possible solution? For instance, I could query the data in the NoSQL and then perform a parallel sum of some sort using spark. Is this a good design idea? What alternatives are there?
In general, given that this is the scenario, do you have any suggestion in mind?
Thank you for your patience
r/DatabaseHelp • u/AndrewLpk • May 24 '20
Help me identify what program/DBsoftware created this file
Trying to figure out what database program created this .DAT file so I can view it in a way that is comprehensible/readable. Opening with notepad results in a messy blob of text data. For some reason reddit wont let me attach an image. I will have to get the file hosted and linked somehow
r/DatabaseHelp • u/IQueryVisiC • Feb 17 '22
1000 tables and growing
I interviewed with jobs to maintain software. That software just grows over the years. So more modules get added and more tables. Now with modules we have import and export and paths. And there is this rule that there should be about 7 items in each folder. Nosql and file systems use folders and can be managed. People complain about file system that multiple users can access a file, but I only had good experience with files. Either the database was the only user with access to that file, or I profit from producer consumer pattern for example with tsc.
Anyway, this relational thing means that every field is a relation to somewhere. Date is a relation to the Gregorian Calendar. Int16 is a link to 65336 numbers. So complex types like DateTime nor string are not allowed. Something like in a pure relational database .. I don't even understand. You need to have a least a dictionary. I mean, what does string ( VARCHAR ) relate to? Binary objects are allowed. So Lists are allowed .. all very confusing.
At least if we only store relations inside a relational database and maybe have translation tables on the "edge", I can see how with entity relationships and generally they way schema are drawn that it is just one large map: Spaghetti. And people like it. I never read that there can be highways or groups or cities. With code we have a lot of ways to decouple stuff: Publisher, subscriber. Encapsulation. Base classes cannot inspect derived classes. Generics.
r/DatabaseHelp • u/Gobuupergetaman • Aug 10 '17
Trying to set up a DB to monitor my spending but not sure how to pull in data from my bank.
I’m trying to set up a small pet project DB to monitor my spending but not sure how to pull in data from my bank.
I’ve been studying SQL and database administration, and I’m looking for a pet project database to maintain. I thought it would be nice to monitor my spending through a database that could then help me budget and possibly alert me to overspending etc.
The DB creation and maintenance would be no problem, my issue is how to I get my bank info into the db? I have Chase bank and would want to pull data from there. I’m not a strong coder though I know a little bit of python.
Is there some sort of web scraper that can help me? How can I get the info into my db?
r/DatabaseHelp • u/Coast2CoastGalapagos • Mar 18 '16
Volunteers Need: Volunteer PHP/MySQL Database Developer needed for the Municipal Government of San Cristobal, Galapagos
The Municipal Government of San Cristobal is looking for experienced volunteers to assist in tourism services and promotion through their Department of Tourism and Culture. The ultimate goal of this department is to promote sustainable land-based tourism on San Cristobal Island which requires them to compete with foreign luxury cruise companies. Foreign language aides are essential to meet this goal in order by greatly enhancing the quality of customer service the department is able to provide in person and online. Tasks for these aides will vary based on personal experience and expertise, but all roles play a major part in bringing beneficial, locally sourced tourism to this very special island.
I am posting here because I am from a local volunteer organization and because of budget constraints they asked me to find a Developer to help update aspects of their PHP/MySQL based database for their website. They are currently working on other parts of updating the site as well (looking for volunteers to translate the website into French, German, Portuguese, and Chinese (Mandarin), are looking for volunteers to help with some graphic design, and are hoping to build a live chat question/feature). I would love it if this position was paid, but they don't have the funding for that. However (if you indeed wanted to come to the Islands instead of working remotely just as a good deed or portfolio builder), in return for your volunteer work they would allow you to stay longer than a regular tourist would be allowed
Please comment and PM me with any questions, or you can apply here
r/DatabaseHelp • u/Praery • Apr 15 '23
Building a database to search Excel files
Hello everyone!
I'm pretty new to the world of databases so please bear with me haha
I would like to tackle the following project and would need some suggestions, ideas or just an assessment if this is even feasible as I have imagined.
Currently I have a huge pile of excel files. Some with one spreadsheet, others with several. All basically contain the same kind of data but the tables themselves are mostly structured differently. I would like to be able to combine all of these Excel files and search them all at once.
The way I see it I now have two basic options: One is to develop a procedure to structure all this data and store it in an SQL database or to build a NoSQL database where the individual Excel files can somehow be stored and searched as they are.
Do you see a possibility that it works? What are the requirements for this?
Thanks a lot!
r/DatabaseHelp • u/SeriouslyYoutube • Aug 08 '21
Picking a right database for a billing software
Hi, I'm starting to build a billing software with inventory management in Laravel for a client. This web application will have almost 500-1000 users per day. Pls help me to pick a right database. I'm think to use MySQL for user info and Mongo DB for invoice and stock data. Is it the right way to do this?
r/DatabaseHelp • u/Kamelasa • Mar 14 '18
How should I handle data that includes operators
I can't find the answer to this question, but it must be a non-unique problem. I am doing a volunteer project that involves transforming some data from pdfs and bringing it into a database. I had to create the database (MSAccess). I specified DOUBLE to display the small decimal numbers nicely. But these are water test values. Sometimes they include a < sign, e.g., arsenic tests out as less than some small number. How on earth do I handle this in the database? If I impute "=" as the default value for all the other results, I now have doubled my fields and it's ugly. If I set the column as a text type, it's easypeasy, but now lose the ability to treat those results as numbers and analyze them. What would you do and is there a set answer to this problem? (In case it's not obvious, I have no database training - just used them a lot in GIS work.)
r/DatabaseHelp • u/coder_candyman • Dec 23 '15
PostgreSQL get data as pivot table
r/DatabaseHelp • u/Dry-Beyond-1144 • Sep 22 '23
What is do's don'ts in data base architecture design? (like ERD - 1vs1, 1vsN, NvsN)
r/DatabaseHelp • u/greyloki • Jan 06 '15
Newbie design questions
Hi there,
I thought it might be fun to sit down and try building a database. To make it useful, I thought it would be nice to build something related to my work. I'm a sound engineer. The situation is thus:
On most music festivals, there are a number of bands. Each band will have their own instrumentation, sometimes with their own engineer's preferences for microphones for each instrument. Our patch engineer (the man who plugs things in) will also have his own notes for each band.
We currently manage this by making a spreadsheet for a day of festival'ing, which is then either printed out and distributed, or set up on a PC backstage running a VNC server, which everybody connects to to make and see updates as the day progresses. Here's a screenshot of a smaller gig: http://i.imgur.com/14pYIPj.png
So to explain the layout slightly: Along the top, is the timings for each band. Channel represents the patch channel; this can be any number from around 24 to 60 or so, usually in multiples of 12. The 'Instrument' column (B) represents a 'house patch', which is agreed at the beginning of the festival. 'Box' (Column C) refers to drop boxes used by the patch engineer. These can change; for example, 'Guitar 2' could end up in Yellow box channel 2, rather than (as pictured) red box channel 12.
You can see that as the day moves on, various instruments end up going down the same channel; Band 2 have a Cahon, but bands 5 and 6 have kick and snare drums.
I'm just trying to get a handle on the most effective way to get this format translated into a database - where I think it'd be cool to end up is to have the database itself running on a machine backstage, with everybody else then being able to access some kind of web-based frontend.
So, to database layout:
I presume (from my research) I would be looking for a structure thus:
Creating a copy of the database for each festival, rather than having every festival within on database; there's no need to refer to previous festivals.
Tables A table of microphones (This would include records like Manufacturer, Model, Stock_Quantity, PhantomPower_Boolean)
A table of instruments (Instrument_Name, Stage_Position, Preferred_Stand). This would, I suppose, end up having records added as bands arrive with new instruments (Hurdy Gurdies, Banjeleles, etc etc). It might also contain names for singers' vocals.
A table of bands (BandID, Time_On, Time_Off, Band_Name, Band_Contact_Number, Band_Manager, Band_Engineer_Contact_Number)
A relationship table linking microphones to instruments for a given band? It seems like this would get filled with a lot of information quite quickly - 60 channels per band, ten bands a day, 600 records in one table while everything else will end up with less than a hundred seems disproportionate, but I'm not really sure what the better way is.
Can you offer any advice?
r/DatabaseHelp • u/quiet_IM_WORKING • Feb 14 '14
One table referencing the same primary key twice. Im getting an error.
r/DatabaseHelp • u/Justinitforthejokes • Jan 06 '15
Not sure what I'm looking for, but I need help with a database solution...
I work in the translation field and I'm looking for a solution to manage feedback about translation. Currently end users of an application (in 20+ languages) submit "bugs" via individual Excel files in which they supply a bunch of information. The critical information is the original English sentence, the current translation, and the suggested translation or fix. These end users don't know what the others are commenting on and they all have different preferences so we get lots of conflicting/duplicate requests over time.
Currently we transfer this data by hand into Mantis which we've customized as much as possible to track these bugs and the action we take in response.
I'm looking for a better solution that more easily allows us to determine if and when comments on the same sentence have been submitted before. Any ideas?
r/DatabaseHelp • u/FatLungs • Jan 13 '15
Looking database designs/advice for handling massive amount of image data...
Hello, I have been tasked with outlining some ideas for a database to store scientific image data. I am looking for suggestions for a server architexture + database that will allow for scaling, high avaialbility and preformance. from what i read, it seems to be best practice to store urls or filepaths +metadata in a db that point to the image. I was looking at using MongoDB, Postgresql or something similiar.
I am looking for suggestions, best practices, expirence etc.:
To summerize:
What are some recommended databases that preform well and are scable?
What is the best way to handle TB+ datasets of image data.
What is the best filetype to use (HDF5 vs TIFF vs something else?) in this scenario?
Are there any HW recommendations?
Lastly, ZFS? ext4/5? what type of filesystem is best for this?
Thanks.
r/DatabaseHelp • u/superfly3000 • Jan 12 '15
Requesting assistance in removing a many to many relationship in an ERD
Hi,
For the life of me I cannot figure out how to get rid of the many to many relationship between property owner and property in my ERD. Of course an owner can own more than one property and a property can be owned by more than one person. Everything that I can think of to place between these two (management agreement, owning syndicate) fixes the relationship on the property side, but it always remains many to many on the owners side. Can anybody please point me in the right direction, or show me what it is that I am missing? Here is my ERD
Thank you
r/DatabaseHelp • u/Blemish • Feb 17 '14
Calculation of sum of points allocation to options chosen
Hey folks,
I have a scenario where results from a customer survey is collected and stored in a database.
The customer ranks the service they received and certain points are allocated to each option the customer chooses:
Example under the Helpfulness tab, the customer must choose one of:
Very Helpful
Neutral
Not helpful
Now each item under that list carries point Very Helpful =3, Neutral =2, Not helpful =1.
I have a table which stores the customer responses, (note the the actual customer scores but just items chosen in menu). The responses are stored in a varchar column, so the actual number is not stored.
I have a table which stores all pre-determined scores and its meaning to the database.
Can someone give me ideas on how to logically map a list item to score and sum all the scores for each customer survey ?
Ideas I have so far:
Create a new column in the survey results table to calculate sum of scores.
Create a new table and store scores there.
Any help greatly appreciated... thanks
r/DatabaseHelp • u/scoobiesnacks4u • Jan 28 '24
Version control/management
Tl;dr having excel problems trying to parse feedback and get updated with new versions, is there a better product out there?
Hi all — not even sure if this is the right sub but I’m looking to solve a problem. We currently use excel — where we have various versions of documents — and take the feedback about that document and associate it in the same excel row.
but sometimes a new version is provided, and we need to request new feedback.
we've had someone program a rather complex series of macros to tell us if we need to seek new feedback; however, its cumbersome to use and occasionally breaks excel when we're trying to utilize it.
i have to believe theres a better, streamlined, off the shelf option for this. any suggestions?
r/DatabaseHelp • u/Open-Carry3751 • Jan 26 '24
Help with SQL calculation
Hi! I found out that I had a MySQL-query that calculated the total sum of invoices wrong. I asked ChatGPT why, and I got a new query which works, but I don't understand why it works 😄.
This is the working query: SELECT sum((amount * (1 - discount/100)) + ((amount * (1 - discount/100)) * VAT / 100)) AS totalsum FROM invoice_rows WHERE invoice_no = '$nr'
Why is it calculating (amount * (1 - discount/100)) two times?
Example:
| amount | discount | vat |
|---|---|---|
| 139 | 0 | 25 |
| 0.25 | 0 | 0 |
139 + VAT (25%) = 173.75
Adding 0.25 for rounding, with no VAT. The result should be 174.
r/DatabaseHelp • u/Tired-Med-Student • Mar 21 '24
UML help
I need to do basic UML diagram for a project. I need to make UML for a ER department (no functions, basic level. It’s extra course). Do you have some comments or suggestions for me?
r/DatabaseHelp • u/noobjaish • Mar 20 '24
Can someone ELI5 Intent Locking?
I'm having a hard time understanding how the 3 intent locks work...
Shared Lock is used for Reading where no other Transaction can Write but can Read.
Exclusive Lock is used for Writing where no other Transaction can Read nor Write.
Kindly explain what the other 3 do (I'm losing my mind)...