Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG] Opinioned design #703

Closed
donhuvy opened this issue Jun 11, 2022 · 0 comments
Closed

[BUG] Opinioned design #703

donhuvy opened this issue Jun 11, 2022 · 0 comments

Comments

@donhuvy
Copy link

donhuvy commented Jun 11, 2022 •
edited
Loading

image

This is my entity

USE [rootTenantDB001]
GO

/****** Object:  Table [dbo].[Account]    Script Date: 11/06/2022 14:37:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Account](
	[AccountID] [uniqueidentifier] NOT NULL,
	[AccountNumber] [nvarchar](20) NOT NULL,
	[AccountName] [nvarchar](128) NOT NULL,
	[AccountNameEnglish] [nvarchar](128) NULL,
	[Description] [nvarchar](255) NULL,
	[ParentID] [uniqueidentifier] NULL,
	[MISACodeID] [nvarchar](100) NULL,
	[Grade] [int] NULL,
	[IsParent] [bit] NOT NULL,
	[AccountCategoryKind] [int] NOT NULL,
	[IsPostableInForeignCurrency] [bit] NOT NULL,
	[DetailByAccountObject] [bit] NOT NULL,
	[AccountObjectType] [int] NULL,
	[DetailByBankAccount] [bit] NOT NULL,
	[DetailByJob] [bit] NOT NULL,
	[DetailByJobKind] [int] NULL,
	[DetailByProjectWork] [bit] NOT NULL,
	[DetailByProjectWorkKind] [int] NULL,
	[DetailByOrder] [bit] NOT NULL,
	[DetailByOrderKind] [int] NULL,
	[DetailByContract] [bit] NOT NULL,
	[DetailByContractKind] [int] NULL,
	[DetailByExpenseItem] [bit] NOT NULL,
	[DetailByExpenseItemKind] [int] NULL,
	[DetailByDepartment] [bit] NOT NULL,
	[DetailByDepartmentKind] [int] NULL,
	[DetailByListItem] [bit] NOT NULL,
	[DetailByListItemKind] [int] NULL,
	[Inactive] [bit] NOT NULL,
	[CreatedDate] [datetime] NULL,
	[CreatedBy] [nvarchar](50) NULL,
	[ModifiedDate] [datetime] NULL,
	[ModifiedBy] [nvarchar](50) NULL,
	[SortMISACodeID] [nvarchar](100) NULL,
	[DetailByPUContract] [bit] NOT NULL,
	[DetailByPUContractKind] [int] NULL,
	[AccountNameChinese] [nvarchar](128) NULL,
	[AccountNameKorean] [nvarchar](128) NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
	[AccountID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_AccountID]  DEFAULT (newid()) FOR [AccountID]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_Grade]  DEFAULT ((1)) FOR [Grade]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_IsParent]  DEFAULT ((0)) FOR [IsParent]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_AccountCategoryKind]  DEFAULT ((0)) FOR [AccountCategoryKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_IsPostableInForeignCurrency]  DEFAULT ((0)) FOR [IsPostableInForeignCurrency]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByAccountingObject]  DEFAULT ((0)) FOR [DetailByAccountObject]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_AccountObjectType]  DEFAULT ((0)) FOR [AccountObjectType]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByBank]  DEFAULT ((0)) FOR [DetailByBankAccount]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByJob]  DEFAULT ((0)) FOR [DetailByJob]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByJob1_1]  DEFAULT ((0)) FOR [DetailByJobKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByJob1]  DEFAULT ((0)) FOR [DetailByProjectWork]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByProjectWorkKind]  DEFAULT ((0)) FOR [DetailByProjectWorkKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByProjectWork1]  DEFAULT ((0)) FOR [DetailByOrder]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByOrderKind]  DEFAULT ((0)) FOR [DetailByOrderKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByContract]  DEFAULT ((0)) FOR [DetailByContract]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByContractKind]  DEFAULT ((0)) FOR [DetailByContractKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByExpenseItem]  DEFAULT ((0)) FOR [DetailByExpenseItem]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByExpenseItemKind]  DEFAULT ((0)) FOR [DetailByExpenseItemKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByDepartment]  DEFAULT ((0)) FOR [DetailByDepartment]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByDepartmentKind]  DEFAULT ((0)) FOR [DetailByDepartmentKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByListItem]  DEFAULT ((0)) FOR [DetailByListItem]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByListItemKind]  DEFAULT ((0)) FOR [DetailByListItemKind]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_Inactive]  DEFAULT ((0)) FOR [Inactive]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByPUContract]  DEFAULT ((0)) FOR [DetailByPUContract]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_DetailByPUContractKind]  DEFAULT ((0)) FOR [DetailByPUContractKind]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'PK Tài khoản' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'AccountID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Số hiệu tài khoản' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'AccountNumber'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tên tài khoản' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'AccountName'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tên tài khoản bằng tiếng anh' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'AccountNameEnglish'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Diễn giải' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'Description'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TK Tổng hợp' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'ParentID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cấp bậc' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'Grade'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Là TK tổng hợp' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'IsParent'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Tính chất tài khoản: 0: Dư nợ; 1: Dư có; 2: Lưỡng tính' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'AccountCategoryKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Có hạch toán ngoại tệ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'IsPostableInForeignCurrency'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo đối tượng' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByAccountObject'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Loại đối tượng: 0 - Nhà cung cấp, 1- Khách hàng, 2- Nhân viên' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'AccountObjectType'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo tài khoản ngân hàng' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByBankAccount'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo đối tượng tập hợp chi phí' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByJob'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Chỉ cảnh báo; 1 = Bắt buộc nhập' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByJobKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo công trình vụ việc' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByProjectWork'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Chỉ cảnh báo; 1 = Bắt buộc nhập' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByProjectWorkKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo đơn hàng' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByOrder'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Chỉ cảnh báo; 1 = Bắt buộc nhập' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByOrderKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo hợp đồng' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByContract'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Chỉ cảnh báo; 1 = Bắt buộc nhập' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByContractKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo Khoản mục CP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByExpenseItem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Chỉ cảnh báo; 1 = Bắt buộc nhập' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByExpenseItemKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo đơn vị' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByDepartment'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Chỉ cảnh báo; 1 = Bắt buộc nhập' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByDepartmentKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Chi tiết theo mã thống kê' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByListItem'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0 = Chỉ cảnh báo; 1 = Bắt buộc nhập' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'DetailByListItemKind'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Ngừng theo dõi' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'Inactive'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cột dùng để sort trên báo cáo. Không sử dụng trên giao diện.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account', @level2type=N'COLUMN',@level2name=N'SortMISACodeID'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Danh mục tài khoản' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Account'
GO

When I move from single-tenant to multi-tennant, I must rewire logic. It is very hard to acceptable in reality.

This is standard entity

USE [rootTenantDB001]
GO

/****** Object:  Table [dbo].[Brand]    Script Date: 11/06/2022 14:38:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Brand](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Description] [nvarchar](max) NULL,
	[TenantKey] [nvarchar](max) NULL,
	[CreatedBy] [uniqueidentifier] NOT NULL,
	[CreatedOn] [datetime2](7) NOT NULL,
	[LastModifiedBy] [uniqueidentifier] NOT NULL,
	[LastModifiedOn] [datetime2](7) NULL,
	[DeletedOn] [datetime2](7) NULL,
	[DeletedBy] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

40% entities in reality not use 1 PK (1 fields), they usually use 1 PK compose primary key (2 fields).

The design is not acceptable. We need rewrite all when move from single-tenant to multi-tenant architect. We need solution for keep old database schema of per tenant.

We have 250 tables, 200 views, 100 custom functions, 350 store procedures. We cannot rebase all database for multitenant.

image

Let's see another solution in Java Spring, we no need redesign database. Just switch connection string, very simple.

image

Another problem with StyleCop: dotnet/efcore#28187
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@donhuvy @iammukeshm