normalization with a twist-plz help

[hyperionfall]

ok this is about to turn my grey hair blue.
what i have is this situation. how to track inventory consumption from multiple remote users reporting to a single place on sharepoint. operations managers need to be able to view usage and status of each remote user.
1. main inventory on sharepoint.
2. multiple usres in the feild.(consumers) update to share point.
3. ops managers at main office. (live conection to sharepoint)
4. remote users may not have live connection all the time to sharepoint but will be able to update on regular basis(24 hour window).
5. new remote user must be able to install (control panel/back end) without being able to edit tables or forms.
6. ops manager must be able to see specific data for each remote user.

my main issue is how to set up data modeling and normilization to include 50 + and growing users consuming 1000s of different parts. i need to track quantity on hand, on order quantity, back order quantity etc.. for each part on their system. everyway i have treid will excede 255 columns in a table. if i columnize by systems aand put the part in rows it will exceede 250, and there are 1000s of parts so cant columnize that. tried looking at unique table for each remote user but i have the issue of how to autogenerate the table in the remote users app and then linking it to sharepopint (remote users will have no access to design features). i seem to end up chasing my own tail every direction i go.
could some nice person please help out a rookie here.


thanks,

lee

 

[MSAccessRookie]

ok this is about to turn my grey hair blue.
what i have is this situation. how to track inventory consumption from multiple remote users reporting to a single place on sharepoint. operations managers need to be able to view usage and status of each remote user.
1. main inventory on sharepoint.
2. multiple usres in the feild.(consumers) update to share point.
3. ops managers at main office. (live conection to sharepoint)
4. remote users may not have live connection all the time to sharepoint but will be able to update on regular basis(24 hour window).
5. new remote user must be able to install (control panel/back end) without being able to edit tables or forms.
6. ops manager must be able to see specific data for each remote user.

my main issue is how to set up data modeling and normilization to include 50 + and growing users consuming 1000s of different parts. i need to track quantity on hand, on order quantity, back order quantity etc.. for each part on their system. everyway i have treid will excede 255 columns in a table. if i columnize by systems aand put the part in rows it will exceede 250, and there are 1000s of parts so cant columnize that. tried looking at unique table for each remote user but i have the issue of how to autogenerate the table in the remote users app and then linking it to sharepopint (remote users will have no access to design features). i seem to end up chasing my own tail every direction i go.
could some nice person please help out a rookie here.


thanks,

lee

Your request appears to be a bit confusing, and may be lacking some information that we will need to assist you. On the surface it appears that at least some basic design changes need to be considered.

• You should consider a Table of Parts witn a new Row for each new Part. Other tables would use to the PartID (PK) when referring to the part.
• You have not presented a reason that sharing a properly split Database will not work for you.
• The number of potential users could eventually create enough of an issue that upgrading to MY-SQL, SQL-Server, or a similar database for the back end, might be a consideration as well.

It was not that long ago that I was the a Rookie here, so I would like to try to help by asking some questions.

[LIST=1]
[*]main inventory on sharepoint.
[LIST]
[*]Do you mean that your Datebase Back End is on Sharepoint, or that the main Database is stored there, and the remote ones get merged into it?
[/LIST]
[*]multiple usres in the feild.(consumers) update to share point.
[LIST]
[*]It sounds like each of these users updates their own individual copy of the database to be merged later.
[/LIST]
[*]ops managers at main office. (live conection to sharepoint)
[LIST]
[*]Live connection to the main database, user database(s), or both?
[/LIST]
[*]remote users may not have live connection all the time to sharepoint but will be able to update on regular basis (24 hour window).
[LIST]
[*]Please elaborate on this requirement.
[/LIST]
[*]new remote user must be able to install (control panel/back end) without being able to edit tables or forms.
[LIST]
[*]Your IT Distribution group should be able to arrange this with the proper installation CD.
[/LIST]
[*]ops manager must be able to see specific data for each remote user.
[LIST]
[*]Live connection to the main database, user database(s), or both?
[/LIST]
[/LIST]

 

[hyperionfall]

thanks for your quick reply

i have my tables split already as you recomended. what i dont have is the quantities set up.
ok to answer your counter questions

1. main inventory on sharepoint.
   • Do you mean that your Datebase Back End is on Sharepoint, or that the main Database is stored there, and the remote ones get merged into it?
     ''''each remote user needs a local db that will be merged to a sharepoint site the remote users do not need to see each others data'''''
2. multiple usres in the feild.(consumers) update to share point.
   • It sounds like each of these users updates their own individual copy of the database to be merged later.
     ''''''that is correct''''''
3. ops managers at main office. (live conection to sharepoint)
   • Live connection to the main database, user database(s), or both?
     '''''ops managers are direct connect to sharepoint only never the reomtes'''
4. remote users may not have live connection all the time to sharepoint but will be able to update on regular basis (24 hour window).
   • Please elaborate on this requirement.
     '''these systems are aboard ships around the world...because of the nature of offshore work a reliable connection can not be considered so i have set up the remote users to work offline and synch with sharepoint when they can. rarely is this longer than 24 hours''''
5. new remote user must be able to install (control panel/back end) without being able to edit tables or forms.
   • Your IT Distribution group should be able to arrange this with the proper installation CD.
6. ops manager must be able to see specific data for each remote user.
   • Live connection to the main database, user database(s), or both?
     ''''i mean that the remote users should be able to update the sharepoint lists so that the ops managers can see all systems data'''

i have split up my tables so that there is 1 for qty 1 for on order qty 1 for back order etc...joined by the id key from the parts table. in each of these tables i have the system name as column and the data in rows. i am not sure if that is good normalization or not. i have a table that lists all systems names in a row. i need to be able to add new columns to each table (programatically hopefully) with the name of the new system as the column header and then be able to oull each tables data into forms and queries etc...i can not find how to create columns in this fasion and i can not figure how to join the system name table to the individual tables to link data...maybe i am going about this all wrong?

thanks