How to set up data elements of a specific organization about common elements?

Question

First post, please be kind.

NOTE. I looked at entry No. 20856 (how to implement tagging), but I feel that it differs in that the tag method that I am considering is specific to the organization in my application. I hope someone can confirm the direction of Im, or indicate other options.

(background). We are creating a web application that provides various information about various organizations in its resources in different places. The database stores users, organizations, sites and elements, as well as links from sites and elements to organizations that allow us to determine which elements / sites will be displayed to users (based on their organization).

Typically, two (or more) organizations want to use the portal to check the stock status (for example) of Widget A at the Los Angeles Warehouse. This part is fine. However, various organizations also track unique information about Widget A. For example, Org 1 wants to track the color, volume, and primary provider for each item. Org 2 wants to track color, stock type, inventory cycle, customer code for each item. I want to avoid the situation where I should have a table loaded with all these possible fields, and then find out which organizations use the fields.

I am considering using something along tag lines, but I add a tag category and define a tag category at the Org level. So, the basic structure of the table will look something like this:

Table: OrgTagCategory
Fields: OrgId, TagCategoryId, CategoryTitle

Table: OrgTag
Fields: OrgId, TagCategoryId, TagId, TagTitle

Table: OrgItemTag
Fields: OrgId, ItemId, TagId

Then, when the user has entered the main dashboard, the grid will include the corresponding element fields in the form of columns in the grid. So, from the above example, Org 1 will see Item #, Description (will be shown to everyone), color, volume and primary provider. Org 2 will display Item #, Description, Color, Stock Type, Inventory Cycle, Buyer Code.

Am I thinking too much about this, or is there an easier way to do this so that I go missing? All thoughts / reviews are sincerely appreciated.

Answer 1

This should not be a problem, but you save OrgId redundantly. It also seems that there may be some overlap (probably a lot of overlap, realistic) between tags and org.

Here's how I would do it:

• Table: OrgTag

  Fields: OrgId, TagId

• Table: Tag

  Fields: TagId, TagTitle

• Table: ItemTag

  Fields: ItemId, TagId

So every org is associated with tags of interest, but you don't have redundant tags. This tag, which is used by several organizations, simply receives a bunch of lines in OrgTag instead of several lines in a tag with the same TagTitle.

You will need the OrgTagCategory table if there are several tag categories for each group. But you did not specify this additional association as a requirement.

Answer 2

Based on your description, I sketched a simplified model and combined it with a watch pattern . This should allow you to track various element properties and user preferences to view them. Admittedly, the Preference table can grow, but the data needs to be stored somewhere, and you can retrieve it using sql, which will simplify the business layer.

- Organization and people are types of users . The User table has columns common to all users , while the Organization and Person tables have columns related to each of them.
- Active element (widget class) can be found on several sites (warehouses); The site stores a lot of elements .
- One item belongs to one user ; user can own many elements .
- Measurement and trait - types of observations. A dimension is a numerical observation, such as height. Value is a descriptive observation, such as color.
- observation has a certain type (height, weight, color), there can be many observations of the same type .
- One element (widget class) can have many observations ; an observation refers to one element only.
- the user may prefer to display many observations ; surveillance may be preferred (for display) by many users .



UPDATE
We could simplify the subscription to user data on details (observations) using tags of the type of observation , for example, height, weight, width will be marked: all, dimensions, physical. Some other tags will be: account_interest, tracking_specific, etc. Then the user subscribes only to tags. Tags (can) form a hierarchy with everyone on top.

- One type of observation (height, weight, color) can have many tags , one tag refers to many types of observations.
- Each tag may have a parent tag forming a hierarchy.
- the user saves the settings for the set of tags that she usually tracks.



UPDATE 2
Now we can figure out who is who and who owns. In this modification, the user (now a person) can work for more than one organization (having several incomplete work orders or contracts). Element now refers to the organization . A registered user can see all the elements from the entire organization in which she works.

Answer 3

My first quick step in this will be that - if it is only limited to “showing” certain fields for certain Orgs on the Dashboard, then it is better to handle it from the App side. If there is another use of "tagging", then pls clarify.

Here's a simple approach - you can save the [OrgDashboardFields] field in the main Org table or in the OrgItem table. This will be a comma separated list (',') of the list of fields that will be displayed on the dashboard. At run time, select the [OrgDashboardFields] field and analyze the comma-separated list in the application and make the Dashboard Grid accordingly.

Or, if there is a dynamic query structure, then based on the [OrgDashboardFields] field, you can create a dynamic SQL query and get the desired result, which is purely specific to Org.