K-Rise Tables vs JDE Tables
K-Rise maintains items tables that mimic tables in JDE. The K-Rise tables are populated on the K-Rise nightly item import. The item import is a process to create a K-Rise copy of the items from JDE which can be referenced as users browse the site to speed up page load times, reduce the number of direct queries to JDE, and keep item searching functionality intact when JDE is down.
Below is a mapping of K-Rise to JDE tables.
JDE Table Description
Item Branch File
Item Base Price File
K-Rise then uses Solr, a powerful tool used to search database records very quickly to improve the user experience. Solr is used in all of our installations to speed up searches and provide functionality that would be difficult to achieve with SQL. When a user searches on the site a Solr query is ran which very quickly searches the data.
The K-Rise nightly item import pulls in all item records we will show on the site to the application SQL Database, but then the table is synced with Solr so that Solr has access to the same records. The records Solr has access to are slightly different than what is stored in the K-Rise database.
Below is an example of the types of records that exist in the K-Rise item tables.
Loading Data Into Solr and Duplicating Records
When K-Rise loads the records from these tables into Solr, a query is ran to create the records Solr will store. The query joins the item tables on their primary keys like below.
In the example where one item has two branch plants and each branch plant has two currencies, one item will have four records. Below is the result of the query that is run.
If Solr now has four records for each item, if we query Solr for item “180001”, it will return all four. To users of the site that would expect only one record returned in the catalog for item “180001”, this would be very confusing.
To avoid this, K-Rise queries Solr with filters on Branch Plant and Currency. This can be done because each user browsing the site has a currency and branch plant associated based on their account number. When a particular user browses the site, K-Rise can query solr for the item that is available from that user’s branch plant and in that user’s currency. This allows Solr to only return one record.
For each aspect of the items which can duplicate records, such as branch plant or currency, K-Rise adds a filter to the query against Solr to ensure no record duplication is shown through in search results to end users.
Loading Data Without Record Duplication
The previous example discussed duplicating records and how K-Rise changes the query against Solr to handle this. However, this is not needed if the joining of K-Rise item tables does not duplicate records.
In the example below, item 180001 only has one branch plant and only one currency. The queries against Solr would not need to specify a Branch Plant and Currency in order to retrieve one record, but if the item catalog ever could contain multiple branch plants or currencies, it might be a good idea to leave the filters in order to allow for growth in the catalog.
Aside from the data pulled in from JDE, K-Rise also stores additional columns in the K-Rise item tables. One of those columns is the CatalogName. The CatalogName is just a string that K-Rise uses to differentiate between various subsets of items. Since this CatalogName definition does not come from JDE, it gives the site some flexibility on defining custom catalogs for users of the site.
Catalog Names are very useful when the items approved for use on the K-Rise site can be divided into groups which certain users should have access to. This approach starts by looking at the items and dividing the full group of items up into Catalogs. Later an approach will be discussed which starts by looking at the users who will browse the site and what items they have access to.
Catalogs can be used where the items in each catalog are entirely distinct and not shared between Catalog Names.
For example, if K-Rise offers an EASYCommerce site to a parent company whose JDE stores information for multiple subsidiaries, items from one subsidiary could be marked with a CatalogName to ensure only users from that subsidiary can see it.
The Catalog Names can be used in combination with record duplication on account of Branch Plants or Currencies. See below a more complicated set up using both CatalogNames and Branch Plants.
Using the CatalogName feature, one site can be used to serve users from multiple subsidiaries.
Another example would be limiting users to only seeing certain brands based on their billing address. If a user in California is limited from purchasing/viewing items that belong to a certain brand and all other users cannot purchase/view those items, two CatalogNames can be created: California and NotCalifornia.
If items can exist in multiple defined Catalog Names, K-Rise can use the same approach as Distinct Catalogs with no issue. This is just something K-Rise needs to be aware of because the processing on the item import changes.
On item import, K-Rise pulls all items in from JDE that have been approved for use on the K-Rise site. Let’s say 1000 records are retrieved, the K-Rise item import process takes one record at a time and performs various actions. One of those actions will be asking “which Catalog Name does this item fall into?”. If an item falls into multiple Catalog Names, the item import will need to insert multiple records into a K-Rise table to store that association.
Here is an example to show what that record duplication looks like. Notice there are still 3 CatalogNames, one for each Subsidiary, but not all can see the same subset items.
Account Level Catalogs
It may be the case where users browsing the K-Rise site will have a personalized catalog for their account (JDE Address Number). One account’s catalog may be similar to another’s, but it should be a rare case that they are ever the same.
Because of this, using the previous CatalogName approach of starting with the items and dividing them into catalogs would result in too many duplicated records and would not be efficient. Since every user needs their own catalog, the item import would need one CatalogName for each account who can view it.
Instead, to implement Account Level Catalogs in the records loaded into Solr, K-Rise has an entirely different table for recording who-can-see-what called EC_Item_Accounts. Below is a sample of what this table contains.
In this example, the account with address number 4242 can see all three items, but 4243 can only see one item. Since the table is very simple, it really is just acting as a flag for the items to record who can see what. Because of this, we are able to simplify the records loaded into Solr which reduce the need for duplicating records.
Notice the column name has become “Address Numbers”. This is because it now stores a list of address numbers that have access to the item.
The previous Catalog Design for Duplicating records by joining tables could not use this method because by joining tables, we gained new columns from the tables that were not part of the primary key. Below is an expanded example of the joined records. The highlighted columns are not primary keys, but are columns that become available as a result of the joins.
The joining of tables was necessary because we want to preserve these column values.