Help with the design, structure and data entry for a data base of spare parts
- or -
Post a project like this2407
£100(approx. $124)
- Posted:
- Proposals: 6
- Remote
- #1685941
- Awarded
Exp 10+ years. US Healthcare, US Hotels, Web Application, Desktop Application, Microsoft .Net framework based technologies, Data warehousing, SSIS,ETL, MS SQL Server, Bigdata using MS Azure HDInsight
Ahmedabad
1519813275591247632133619816460921726988
Description
Experience Level: Expert
General information for the business: This is a personal project to assist owners of a particular range of pre-1940 vehicles
Description of requirements/functionality: I have a Microsoft Access 2016 data base with a table containing approximately 20,000 spare parts that were produced over a 20 year period for approximately 20 different models. The spare parts data has been manually entered from spare parts lists produced from 1921 to 1940, The long term aim is to produce a good looking web page where it will be possible to find spare part information for a particular year and model and I already have a rough and ready site in operation using a MySQL data base with tables exported from Access.
For this job, I need help to create an easily maintainable structure for this data base that can be further expanded in the future. I will need a design that includes a table that links each part record with each of the different models it was used on and also links to the original document and a jpg of the page of the original document. To do this the data base designer will need to look through the parts lists and understand how different models from different years used combinations of the different parts. Once a particular part has been identified it will be possible to show where else that part was used. After the design of the tables is agreed I also require some tools or forms that will allow the transactional table/tables to be populated and maintained. It is quite possible that 70-80% of this can be done using some clever SQL as models types are often embedded in the data but for sure some manual input and corrections will be needed as the original parts book information isn't particularly well organised or consistent.
To be clear, I don't expect any data entry to be performed for this job but would expect to be in a position with the necessary tools to either begin the process myself or pass the work on.
I hope this is sufficient information but I will try to answer any questions.
Specific technologies required: MySQL Microsoft Access
Extra notes: Please look at www.sunbeamland.com/partslist which will provide an idea of how the data is organised. This is just a test page and is not representative of how the final page will look.
I have the following tables already:
A part table of all the parts for all models and years which includes all the original manufacturers information about the part. Depending on which year the 'model list' may or may not include information about which model the part is for. Sometimes it is implied by the text in 'model list' i.e. "Any", sometimes by the part 'Description', other times by the text in the 'Section' field and other times by the document the part is from.
ID
Date
Order reference
Section
Sub-section
Description
Notes reference
Quantity
Price
Model list
Part Number
A document table which defines the individual parts lists:
ID
Type
Date
Model year start
Model year end
Supplement to
Parts table hyper-link
Title
I have then created a 'Document to model' table that links which models and variants are referenced in each parts list:
ID
Model ID
Document ID
Variant ID
And a 'Document to year' table that defines which model years are covered by each document as often a document covers a spread of years.
ID
Document ID
Model year
We wish to drill down and find parts by first selecting model base then year. This will give us the range of variants available for that year and the user can select the variant required. We will then display any "sections" headings that relate to that model variant if any exist in the document we are referencing for that model year and variant. You can see the section headings on the example web page. I propose that parts without a "section" are lumped together under "general parts" as per the web page. Maybe we need a separate lookup table for sections? I don't know. Finally, we list the parts for the selected model, year, variant and section. If the user selects one of the parts we will automatically show the jpg of the original document, a photo of the part if one exists and list all the other models on which that part is used.
ID
Year
Model base
Model variant
Document ID
Section ID?
Part ID
Document page jpg
Part photo
To clarify, the job is to help with designing the structure and create some data entry / management tools (forms?) to enable the work of connecting the part data to models, years and documents be performed efficiently and so that it can be maintained in the future. I'm using MS Access to play with the data and my web provider offers the MySQL data base I'm using for the demo so it would be nice but not essential to use these tools. I will be posting a separate job later to create a nice looking web page and obviously it would be great if the same person could carry on to this but it is not essential.
Description of requirements/functionality: I have a Microsoft Access 2016 data base with a table containing approximately 20,000 spare parts that were produced over a 20 year period for approximately 20 different models. The spare parts data has been manually entered from spare parts lists produced from 1921 to 1940, The long term aim is to produce a good looking web page where it will be possible to find spare part information for a particular year and model and I already have a rough and ready site in operation using a MySQL data base with tables exported from Access.
For this job, I need help to create an easily maintainable structure for this data base that can be further expanded in the future. I will need a design that includes a table that links each part record with each of the different models it was used on and also links to the original document and a jpg of the page of the original document. To do this the data base designer will need to look through the parts lists and understand how different models from different years used combinations of the different parts. Once a particular part has been identified it will be possible to show where else that part was used. After the design of the tables is agreed I also require some tools or forms that will allow the transactional table/tables to be populated and maintained. It is quite possible that 70-80% of this can be done using some clever SQL as models types are often embedded in the data but for sure some manual input and corrections will be needed as the original parts book information isn't particularly well organised or consistent.
To be clear, I don't expect any data entry to be performed for this job but would expect to be in a position with the necessary tools to either begin the process myself or pass the work on.
I hope this is sufficient information but I will try to answer any questions.
Specific technologies required: MySQL Microsoft Access
Extra notes: Please look at www.sunbeamland.com/partslist which will provide an idea of how the data is organised. This is just a test page and is not representative of how the final page will look.
I have the following tables already:
A part table of all the parts for all models and years which includes all the original manufacturers information about the part. Depending on which year the 'model list' may or may not include information about which model the part is for. Sometimes it is implied by the text in 'model list' i.e. "Any", sometimes by the part 'Description', other times by the text in the 'Section' field and other times by the document the part is from.
ID
Date
Order reference
Section
Sub-section
Description
Notes reference
Quantity
Price
Model list
Part Number
A document table which defines the individual parts lists:
ID
Type
Date
Model year start
Model year end
Supplement to
Parts table hyper-link
Title
I have then created a 'Document to model' table that links which models and variants are referenced in each parts list:
ID
Model ID
Document ID
Variant ID
And a 'Document to year' table that defines which model years are covered by each document as often a document covers a spread of years.
ID
Document ID
Model year
We wish to drill down and find parts by first selecting model base then year. This will give us the range of variants available for that year and the user can select the variant required. We will then display any "sections" headings that relate to that model variant if any exist in the document we are referencing for that model year and variant. You can see the section headings on the example web page. I propose that parts without a "section" are lumped together under "general parts" as per the web page. Maybe we need a separate lookup table for sections? I don't know. Finally, we list the parts for the selected model, year, variant and section. If the user selects one of the parts we will automatically show the jpg of the original document, a photo of the part if one exists and list all the other models on which that part is used.
ID
Year
Model base
Model variant
Document ID
Section ID?
Part ID
Document page jpg
Part photo
To clarify, the job is to help with designing the structure and create some data entry / management tools (forms?) to enable the work of connecting the part data to models, years and documents be performed efficiently and so that it can be maintained in the future. I'm using MS Access to play with the data and my web provider offers the MySQL data base I'm using for the demo so it would be nice but not essential to use these tools. I will be posting a separate job later to create a nice looking web page and obviously it would be great if the same person could carry on to this but it is not essential.
Sean K.
90% (2)Projects Completed
1
Freelancers worked with
1
Projects awarded
50%
Last project
7 Jun 2017
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
Hi Sean
Is this the same job as advertised back in June, please?
Thanks
Ian CSean K.23 Aug 2017Hi Ian. Yes, that is correct, this is the same job as advertised back in June.
486866
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies