CODEWITHSUNDEEP
sqlsql-serverdatabase
Vinz and Tonz
Vinz and Tonz

Reputation: 3587

SQL Server database redesign ideas

I have a table which contains a list of menu items. This menu item table is related to 5 other tables like menu content table, menu download table. Right now we got a new requirement to add one more layer saying each menu can be associated with multiple technical codes. I am looking for a optimized DB restructuring without affecting existing data.

Menu table:

MenuID MenuName
----------------
1       Menu1
2       Menu2

Menu Content table:

MenuContentID MenuID MenuContent
---------------------------------
1              1      contentsss
2              2       content2

Now I need to add a new relation with techcode. For each menuname we can have two versions. My application is in production. So I am thinking an optimized way to redesign my database and application.

First approach is adding a code and adding tech_code to menuitem table and group based on code.

MenuID Code MenuName  Tech_Code
-------------------------------
1       M   Menu1      A
2       M   Menu2      B
3       A   Menu3      A

MenuContentID MenuID  MenuContent
---------------------------------
1              1      contentsss
2              2      content2

2nd option is create a parent table and when we query data query using that parent table id

ParentMenuID MenuID 
-------------------
1                1
1                2

MenuID ParentMenuID  MenuName  Tech_Code
----------------------------------------
  1       M          Menu1        A
  2       M          Menu2        B
  3       A          Menu3        A

3rd design (add Tech_Code to referencing tables)

MenuID  MenuName
-----------------
1       Menu1
2       Menu2

MenuContentID MenuID   MenuContent  Tech_Code
----------------------------------------------
1              1       contentsss      A
2              2       content2        B

Which will be the best way to implement this? Please give me your suggestions.

Upvotes: 0

Views: 287

Answers (2)

Hope Mystery
Hope Mystery

Reputation: 338

Here you can specify code type: Tech Code or Menu Code or .... etc

Each menu has its Code + Content

And if you want the menu have two types of codes at the same time, you can have two tables like CodeList as "MenuCodeList" and "TechCodeList" and remove CodeList and add their ids to Menu_Code_Content and no need for the CodeType if the only needed the "MenuCodeList" and "TechCodeList" tables alone

enter image description here

Upvotes: 1

Anagha
Anagha

Reputation: 918

As you said "each menu can be associated with multiple technical codes" so your First option can not work and your second option is little confusing so you should go for 3rd option or i have another suggestion create one table like this:

Tech_Code MenuID  Code 
-------------------------------
  A       1       M
  B       1       K

Here, Tech_code is primary key which can associate with same menu. I think, Creating new table is less risky as you are working in Production DB.

Upvotes: 0

Related Questions