July 24, 2018 at 3:42 am
Hi All,
Need some table design advice.
I our environment, there is functionality call rules and actiontypes. We create rules (also called conditions) and once a rule/condition is met an Action is performed.
Action is of 3 types. ClearAnswer,DefaultValue & Formatting. So, currently, for each Action Type, 3 tables are maintained. I dont know the reason behind it.
I tried checking with some existing vendors, they dont have much infomation. They just say for simplicity sake and distinguish between the Action types there are using three separate tables.
But, my observation looking at the 3 tables is, most of the columns are repeated ones.
Now my Question is, can't we have only 1 table to hold all the information. We should be filling NULLs for columns which are not applicable for that specific Action type?
Which is better approach? Anyone sees any pros n cons with these 2 approaches?
Reason why i am asking is, now a new ActionType as come in, now they want to create a new table again? They say, they are just following the way which has been done earlier for the old/previous Action types and don;t want to go with one single table.
Can't able to convince them which approach is good.
CREATE TABLE [ClearAnswerActionType](
[Id] [int] NOT NULL PRIMARY KEY,
[ActionId] [int] NOT NULL,
[CreatedAt] [datetimeoffset](0) NOT NULL,
[CreatedBy] [int] NOT NULL,
[ModifiedAt] [datetimeoffset](0) NULL,
[ModifiedBy] [int] NULL,
[DeletedAt] [datetimeoffset](0) NULL,
[DeletedBy] [int] NULL,
[IsDeleted] [bit] NOT NULL,
[IsSynced] [bit] NOT NULL,
[TargetQuestionId] [int] NOT NULL
)
GO
CREATE TABLE [SetDefaultValueActionType]
(
[Id] [int] NOT NULL PRIMARY KEY,
[ActionId] [int] NOT NULL,
[CreatedAt] [datetimeoffset](7) NOT NULL,
[CreatedBy] [int] NOT NULL,
[ModifiedAt] [datetimeoffset](7) NULL,
[ModifiedBy] [int] NULL,
[DeletedAt] [datetimeoffset](7) NULL,
[DeletedBy] [int] NULL,
[IsDeleted] [bit] NOT NULL,
[IsSynced] [bit] NOT NULL,
[DefaultValue] [nvarchar](max) NULL,
[TargetFieldId] [int] NULL,
[TargetRowFieldId] [int] NULL,
)
GO
CREATE TABLE [FormattingActionType]
(
[Id] [int] NOT NULL,
[ActionId] [int] NOT NULL,
[CreatedAt] [datetimeoffset](7) NOT NULL,
[CreatedBy] [int] NOT NULL,
[ModifiedAt] [datetimeoffset](7) NULL,
[ModifiedBy] [int] NULL,
[DeletedAt] [datetimeoffset](7) NULL,
[DeletedBy] [int] NULL,
[IsDeleted] [bit] NOT NULL,
[IsSynced] [bit] NOT NULL,
[FormattingOptionId] [int] NOT NULL,
[TargetFieldId] [int] NULL,
[TargetHeaderColumnId] [int] NULL,
[TargetRowFieldId] [int] NULL,
)
GO
Thanks,
Sam
July 24, 2018 at 4:20 am
I think the better answer here would be to have 1 table with a column for the action. I note that these tables share the majority of columns, and those that they do are NULLable (apart from TargetQuestionId and FormattingOptionId). You could, therefore, quite easily set this up as one table, and keep the NULLability with a CONSTRAINT. This means your product is scaleable, regardless of if you have 1 or 100 more action types (provided they are all very similar).
This results in DLL that looks something like:CREATE TABLE dbo.Actions ([Id] [int] NOT NULL,
[ActionType] varchar(20) NOT NULL, --New column, which would previously have been part of the table name
[ActionId] [int] NOT NULL,
[CreatedAt] [datetimeoffset](7) NOT NULL,
[CreatedBy] [int] NOT NULL,
[ModifiedAt] [datetimeoffset](7) NULL,
[ModifiedBy] [int] NULL,
[DeletedAt] [datetimeoffset](7) NULL,
[DeletedBy] [int] NULL,
[IsDeleted] [bit] NOT NULL,
[IsSynced] [bit] NOT NULL,
[DefaultValue] [nvarchar](MAX) NULL,
[TargetQuestionId] [int] NULL, --NULLability will be addressed
[FormattingOptionId] [int] NOT NULL, --Nullability will be address
[TargetFieldId] [int] NULL,
[TargetHeaderColumnId] [int] NULL,
[TargetRowFieldId] [int] NULL);
GO
ALTER TABLE dbo.Actions
ADD CONSTRAINT TargetQuestionId_nullable CHECK (CASE WHEN [ActionType] = 'ClearAnswer' AND TargetQuestionId IS NULL THEN 0 ELSE 1 END = 1);
ALTER TABLE dbo.Actions
ADD CONSTRAINT FormattingOptionId_nullable CHECK (CASE WHEN [ActionType] = 'Formatting' AND TargetQuestionId IS NULL THEN 0 ELSE 1 END = 1);
You can now easily add your 4th action, as you simply use a different value for the ActionType Column.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 25, 2018 at 12:19 am
Thanks a ton Tom. One last question, what are the potential drawbacks of having separate tables for each action type?
July 25, 2018 at 3:44 am
vsamantha35 - Wednesday, July 25, 2018 12:19 AMThanks a ton Tom. One last question, what are the potential drawbacks of having separate tables for each action type?
A couple of thoughts:
Like I mentioned before, scalability is going to be one issue. As and when you add new actions, you need to create new tables, and then update any and all SQL you have that need to reference that (and other) actions.
Also, with a table for each, if you're running a query against multiple of those tables (which I assume is a safe assumption, otherwise this wouldn't be a question), tthen you having to do a JOIN against each, and possibly a LEFT JOIN and then cater for NULLs in the WHERE. This'll require 1 scan/lookup per action type, rather than 1 scan/lookup on a single table that contains all of them.
The latter is a bit of a guess, based on your structure and queries that you are running.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 25, 2018 at 6:05 am
Thank you very much Sir.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply