I worked in several companies and in each of them audit tables have been storing full snapshots of records for every change. To my understanding it’s enough to store only changed columns to recreate record in any given point of time. It will obviously reduce storage space. Moreover I suppose it would im…
Tag: relational-database
Aggregate Function Error With Basic Query
My database is based on a cinema, I am trying to make a query which displays the number of tickets purchased for each movie screening. The tables involved in this query are Bookings, Screenings and Movies. Here is the Bookings table: The Screenings table: And the Movies table: I have so far managed to get the…
Relational Databases: Is it fine to have a foreign key referencing an Associative table?
Lets say we have Passengers, where Passengers can attend many Flights, and one Flight may have many Passengers. In each Flight, each Passenger has a unique selection of Movies he/she can watch. For Passengers, Flights, and the association between them, we have three tables: For Movies, it must specify for whi…
MySQL: Select only if field values are ascending
I have two tables: Route and Stop (one to many). And also I have input data: two location IDs which is related to ‘stop’ table. Every route has it’s own ORDERED list of stops. I need to select Route …
rails multiple has_many relationships between the same models
I’m trying to figure out the best way to model a many-to-one relationship in rails where there are multiple scopes to the relationship. An example would be a restaurant has-many photos. I want to be …
Joining in SQLite to get desired records
I am working on a mobile application which takes two inputs – Source Station Name and Destination Station Names. Upon receiving these two inputs, the application would then enlist the names of trains …
Nested Assemblies Database Structure
I’m creating quoting software for the manufacturing business I work for and I’m unsure how to handle assemblies within my MySQL database structure as they are a little like a Russian Doll. Here’s a …
Postgresql delete multiple rows from multiple tables
Consider 2 or more tables: I wish to delete all users and their orders that match first name ‘Sam’. In mysql, I usually do left join. In this example userid is unknown to us. What is the correct format of the query? Answer http://www.postgresql.org/docs/current/static/sql-delete.html You can also …
PostgreSQL Foreign Key syntax
I have 2 tables as you will see in my PosgreSQL code below. The first table students has 2 columns, one for student_name and the other student_id which is the Primary Key. In my second table called tests, this has 4 columns, one for subject_id, one for the subject_name, then one for a student with the highest…
Modeling a prescription/medicine usage set of database tables
Not sure how to go about modelling medication taken at a point in time in a relational database. Each way I have thought of so far seems to have redundant data. Need to be able to have a user say I …