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
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
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
The problem is I'm not able to determine how should I use the foreign key on
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
I'm not able to figure out foreign keys between
Is my design work? am I thinking right? can anyone suggest a more efficient design?