Need suggestion moving from relational DB to Couchbase

Couchbase Server
1

Hello,

We are currently working on a pilot project to pick a small project that 3-5 table in DB2 and we are planning to move this to couchbase
and we are in evaluating to achieve with better results. Note:- I am new to couchbase. Below I have provide sample of application with required
details

Section table (Table 1)

SEC_NBR_ID (PK)
SEC_TXT
ADD_TS

Sub-Section table (Table 2)
SUB_SEC_ID (PK)
SEC_NBR_ID (FK) reference table1
SUB_SEC_TXT
ADD_TS

Article (Table 3)
ARTICLE_ID (PK)
SUB_SEC_ID (FK) reference table2
ARTICLE_TXT
ADD_TS

The above piece of high level information.

We are looking to move this to couchbase.

We have created a cluster and a bucket and inside it I have two approaches to achieve this.

Note: All the id’s will unique (ignore the duplicates here).

Approach 1:

Document#1

{
	"id":"SEC_1";
	"category":"SEC",
	"desc":"Section 1"

	"subSections":[
		{
			"id":"SUBSEC_1",
			"category":"SUBSEC",
			"desc" : "Sub Section 1"
			"articles" : [
				{
					"id":"ART_1",
					"category":"ART",
					"desc" : "Article 1"
				},
				{
					"id":"ART_2",
					"category":"ART",
					"desc" : "Article 2"
				},
				{
					"id":"ART_3",
					"category":"ART",
					"desc" : "Article 3"
				}
				]
			
		},
		{
			"id":"SUBSEC_2",
			"category":"SUBSEC",
			"desc" : "Sub Section 2"
			"articles" : [
				{
					"id":"ART_40",
					"category":"ART",
					"desc" : "Article 50"
				},
				{
					"id":"ART_50",
					"category":"ART",
					"desc" : "Article 60"
				},
				{
					"id":"ART_60",
					"category":"ART",
					"desc" : "Article 60"
				}
				]
			
		}]
}

Document#2
{
	"id":"SEC_2";
	"category":"SEC",
	"desc":"Section 1"

	"subSections":[
		{
			"id":"SUBSEC_1",
			"category":"SUBSEC",
			"desc" : "Sub Section 1"
			"articles" : [
				{
					"id":"ART_1",
					"category":"ART",
					"desc" : "Article 1"
				},
				{
					"id":"ART_2",
					"category":"ART",
					"desc" : "Article 2"
				},
				{
					"id":"ART_3",
					"category":"ART",
					"desc" : "Article 3"
				}
				]
			
		},
		{
			"id":"SUBSEC_2",
			"category":"SUBSEC",
			"desc" : "Sub Section 2"
			"articles" : [
				{
					"id":"ART_40",
					"category":"ART",
					"desc" : "Article 50"
				},
				{
					"id":"ART_50",
					"category":"ART",
					"desc" : "Article 60"
				},
				{
					"id":"ART_60",
					"category":"ART",
					"desc" : "Article 60"
				}
				]
			
		}]
}

Approach two: (break more small into individual document) Above document#1 will broken as shown below

Document#1
{
“id”:“SEC_1”;
“category”:“SEC”,
“desc”:“Section 1”
"subSections:[{“SUBSEC_1”, “SUBSEC_2”…}]
}

Document#2
{
“id:”:“SUBSEC_1”,
“category”:“SUBSEC”,
“desc”:“Sub section 1”,
“articles”:[{“ART_1”, “ART_2”, …}]
}

Document#3

{
	"id:":"SUBSEC_2",
	"category":"SUBSEC",
	"desc":"Sub section 2",
	"articles":[{"ART_11", "ART_22", .....}]
}

Document#4

{
	"id:":"ART_1",
	"category":"ART",
	"desc":"Article xx",
	
}

Document#4

{
	"id:":"ART_2",
	"category":"ART",
	"desc":"Article yy",
	
}

These two approaches are the one that we had in mind. In first approach each document will be big but writing queries was much easy. In the second
one document size will be less but lot documents will be created. Also we are not able to get the data say e.g get all the articles for a given subsection,
or get all subsections for a given section. If there is a solution do let me know. Also which one will performance wise better. We will have
lot data in each categories. Please advisee the above two approaches looks ok or is there is any better approach to achieve the same.

Much Thanks

2

Second approach is better as you can join multiple table using JOIN Clause, you just need to learn how you can do the same using N1QL query

first you make a function using your preferred programming language which can convert SQL table rows into a json file for each rows .

then using N1QL JOIN you can get your table row back using query

3

@y2k1975 The best approach depends on your workload. Joins are more expensive than fetching a single document with nested sub-documents, but if you frequently need to look at the subdocuments independent of their parent object, having them nested can be more expensive. You need to figure out the frequency of different types of queries to decide which option to choose.

Also, it might be worth loading a sample data set using both approaches and then see how they perform with your anticipated workload.