UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Structure - Multivalue Columns Vs A Ton Of Queries, SharePoint 2013    
 
   
silveryne
post Jun 14 2017, 02:22 PM
Post#1



Posts: 63
Joined: 25-June 09



I have a scenario and I am considering the best data structure for this that involves least programming experience and maintenance. Wanting to solicit some suggestions on how it may be improved before too much work goes into it.

The scenario:
- I got a bunch of drawings outlining how products should look like (5000 records)
- I got a bunch of item numbers that refers to different parts (10000 records)
- One part can be associated to multiple drawings
- Each part can only be defined by one drawing
- The drawings cross references each other, and such cross references are recorded
- Each item number is associated with a number of other item numbers based on current and older legacy systems
- Each item number is associated with an item type, each of which have different attributes tagged on to them (right at 100 types of items)
- The data should be hosted on sharepoint for front end access, with site collection administrator privileges but not administrator privileges

Requirements (output):
- People need to be able to find the drawings easily based on the product they are looking at, and vice versa when they are trying to find a product based on a drawing
- Drawing linkages can be queried on the back end to determine update needs when drawings change
- Use keyword search on sharepoint to query all items and return the most relevant results, which can then be used to point people to the right drawings and item number references
- Use keyword search on sharepoint to query drawings to find the most appropriate drawings

Current Data structure Being Considered:
- All drawings are going to be nested in one list on sharepoint, with the cross referenced drawings in a multi value column
- Each item type is given its own table in access with all attributes on it, including associated drawings as a multi value column - this then gets pulled into SP
- A master table is available that indicates what item type needs what type of attributes - more for consistency control of item types as time passes by
- Each item is given a comprehensive description based on the attributes in the table, this is currently approached with a calculated column but facing challenges since it is linked to the type attributes table by ID
- A linked datasource joins all item type tables to grab only relevant fields that can be keyword searched on sharepoint

Problems:
- Access multi value columns are quite not user friendly to work with, but it works great to display data without creating multiple queries and fits great with sharepoint out of the box capabilities
- Each item having its own table is cumbersome, but using excel to host the data is just as bad since the only thing that allows you to connect an excel file and a table without programming is activeX controls on IE - which I fear would disappear one day.
- Calculated column for the comprehensive description is quite difficult, no solution right now
- Populating data for multivalue columns in access is difficult and time consuming (the fields doesn't seem to allow data entries by typing, I have to use the drop down to click the right ones I wanted to select)

Explanations:
Coming from a tradtional database design approach, you would dump all the attributes into a table with attribute ID and value. But such structure requires 100 programmatic manipulations for data to display correctly (or entered correctly) for the sharepoint front end, also custom webparts will be involved if that solution is considered to simply display the aggregated data. So it was considered not ideal due to those reasons since there is no capability currently to do webpart development internally other than client side manipulations.

If you have this project, how will you do it?
Attached File(s)
Attached File  Illustration.png ( 8.68K )Number of downloads: 0
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 05:48 PM