0

DB setup: MSSQL

I have a source record coming in via API call. That call is intended to update a system in the database.

The first table is a common table where all systems have some details

SystemId   | SystemName   | Type      | Memory    | Storage  | Etc......
-----------+--------------+-----------+-----------+-----------
92         | Server1      | BlueType  | 16GB      | 10TB     | 
96         | Server2      | RedType   | 24GB      | 20TB     | 
...
...

Then I have at least 2 tables, each with different fields pertaining to a particular role of the system:

BlueId  | BrokerName   | Threshold | TriggerLevel  | Etc......
--------+--------------+-----------+---------------+-----------
41      | Palooza      | 400       | 4             |  
42      | Almamater    | 900       | 3             |  
...
...
RedId   | Parent      | MetalCast | Available  | Etc......
--------+-------------+-----------+------------+-----------
87      | Pindle      | Yes       | Yes        |  
88      | Feldspar    | No        | No         |  
...
...

The source that is providing the data would be submitting it in one statement. That statement would come from a source that knows what "type" it is (red, blue, etc) and would have the appropriate fields with it as it pertains to that type.

For example, if the statement is coming from a "Red" type of system, the statement would come in similar to the following:

SystemName = CrosserTT1
Type = BlueType
Memory = 20GB
Storage = 10TB
BrokerName = PintoCall
Threshold = 250
TriggerLevel = 6
etc....

This means that I need to take this data, update the "Common" table as above with the appropriate fields, determine what the value of "Type" is and based on the type value update the appropriate table with the appropriate fields. In this example above, I'd want to update the common table, and then update the Blue table based on the value of the type. I wouldn't want to even touch the red table in this case.

These are the things I'm wondering:

  • Is this possible to have an insert statement that in-one-shot can update common, choose which type table to update, then go update that?
  • Should I have a single table that contains all the fields of all the types and only add data into the columns appropriate to the type?
  • Should I have a single table INCLUDING COMMON that has all fields of everything system-related and leave null the fields that don't pertain to a record?

I'm trying to determine the best practice is for this kind of thing - for performance and simplicity.

In terms of the insert statement with the example above, would it make more sense to have a Stored Procedure and provide it all the values instead?

Thanks for your valued input.

If you need clarification, please let me know.

2
  • Also wanted to add that I was hoping to keep the fields that are based on types in a separate table. Otherwise, if I have to add another type with additional fields that only pertain to that type, it would be a problem having to change the design on the "big table" to accommodate it and it would not be scalable. – Clyder Holobrockian Dec 14 '20 at 17:41
  • 1
    Please do not show "must look like" samples. Provide complete initial data (3-5 rows enough), definite input data (1-2 rows) and desired final state strictly for shown initial data and input, with the explanations which refers to definite value(s). Input data must demonstrate any possible situaton (for example, specific part from the input is present and is absent in details table). – Akina Dec 14 '20 at 17:57
0

What you are looking for is called Trigger.

For example:

https://sqlite.org/lang_createtrigger.html

I've wrote a little sample for sqlite, you could repeat everything on any unix/linux system.

The init.sh:

# cat init.sh 

echo "CREATE TABLE 'first_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        type            text DEFAULT 'blue', \
                        blabla          int  DEFAULT 0 \
                        );"

echo "CREATE TABLE 'blue_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 1 \
                        );"

echo "CREATE TABLE 'red_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 2 \
                        );"

echo "CREATE TRIGGER t1 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'blue' \
                BEGIN \
                        INSERT INTO blue_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "CREATE TRIGGER t2 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'red' \
                BEGIN \
                        INSERT INTO red_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "INSERT INTO first_table ( name, type ) VALUES ( 'ololo1', 'red' ), ( 'ololo2', 'blue' ), ( 'ololo3', 'blue' ), ( 'ololo4', 'red' );"

Firstly, here, you are create 3 tables, thereafter 2 triggers, which are fires according to type field, if type is 'red' - fired 1st trigger, if type is 'blue' - another one.

Usage:

# bash init.sh | sqlite3 tmp.db
# echo "SELECT * FROM red_table;" | sqlite3 tmp.db 
1|ololo2|2
2|ololo3|2
# echo "SELECT * FROM blue_table;" | sqlite3 tmp.db 
1|ololo1|1
2|ololo4|1
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.