Need help on designing database schema for high frequency update app

by Jojo   Last Updated August 14, 2019 03:06 AM

This app will call API endpoints every second and get JSON data and it needs to be saved in DB and then query it to the client. But Before saving, it needs to check if the data already exists or not, I'm using hash and calculating its values of only those columns whose values might change to check duplicates and this will keep on repeating every 1 second.

The current design is as follows:

appsmaster
--
id int PK IDENTITY
apps_guid varchar(255)
name varchar(255)
space_guid varchar(255)
foundation varchar(5)
appsmaster_hash binary UNIQUE
timestamp datetime


appsinfo
--
id int PK IDENTITY
app_guid varchar(255)
state varchar(255)
created_at datetime
updated_at datetime
type varchar(255)
stack varchar(255)
apphash binary UNIQUE
timestamp datetime

spaces
--
id int PK IDENTITY
space_guid varchar(255)
app_guid varchar(255)
created_at datetime
updated_at datetime
name varchar(255)
spacehash binary UNIQUE
timestamp datetime

organizations
--
id int PK IDENTITY
org_guid varchar(255)
space_guid varchar(255) FK >- spaces.space_guid
created_at datetime
updated_at datetime
name varchar(255)
orghash binary UNIQUE
timestamp datetime

buildpacks
--
id int PK IDENTITY
app_guid varchar(255) FK >- appsinfo.app_guid
buildpack varchar(20)
buildpackhash binary UNIQUE
timestamp datetime

foundations
--
foundation_id int PK IDENTITY FK >- appsmaster.foundation
name varchar(3)

I'm thinking to keep appmaster as my main table. When I receive data from API its a JSON object which combines appmaster and appsinfo except id, apphash, appsmaster_hash, and timestamp columns but while saving I want to split this data into two columns because if I combine these two columns then I will not be able to use the foreign key on other columns because except id and hash columns all other columns will be repeated and there is no uniqueness and I don't want to use hash as my primary keys for obvious reasons.

The only possible solution I could think of is to split the data into two columns repeating and non-repeating, so if I maintain one master table with app_guid which can be a primary key and then use it as a foreign key in different columns like spaces or appsinfo The problem is I'm not able to determine how should I use the foreign key on spaces and appsinfo table.

The way I get this data is little different I need to make several calls to API endpoints to get this data.

The first call is to get apps from that I get space_guid using that I need to make another call to spaces and then from spaces to organizations.

I'm not able to figure out foreign keys between appsmaster and spaces and appsinfo.

Is my design work? am I thinking right? can anyone suggest a more efficient design?

enter image description here



Related Questions


Updated January 17, 2019 16:06 PM

Updated July 02, 2019 15:06 PM

Updated April 11, 2018 21:06 PM

Updated May 26, 2015 22:02 PM

Updated July 31, 2018 09:06 AM