DB Design: Is a database like this possible?

[Accessnoob]

Hello,
I'm an intern at a aviation company and was tasked to do an evaluation which they thought would be possible because I'm good at vba however there is no way that that kind of data volume can be processed in excel. Thus I'm now tasked with creating a database based on the various excel exports. This is my first contact with database design so please forgive stupidity and please to refer me to good sources for me to read up in case you know any.

Starting Situation:
Half of the data is aircraft related [Identification: AC TYPE]
- Excel Table: Our fleet data [each row has a date]
- Excel Table: Global fleet data [each row has a date]
Half of the data is component related [Identification: PN]
Some components are interchangeable, all interchangeable parts are in a disposet [Identification: DISPOSET]


- Excel Table: Sales data [each row has a date]
- Excel Table: Workshop data [each row has a date]
- Excel Table: Purchase data [each row has a date]
- Excel Table: Market data [each row has a date]

There are just short of 1 million different components that need to be in the database. There are several hundred different ACTYPES. There are several million rows of data from 2017 alone.
The components have a multiple to multiple relation to the ac types. [each PN can be valid for multiple ACTYPE and of course an ACTYPE contains several PN]

Goal:
Database that allows me to create a table that contains all the information for a (or multiple) PNs or DISPOSETs.
The resulting table should contain only one row for each PN/DISPOSET and Month. // The idea behind this is that I use Access to summarize the data into months reducing the size significantly before using Excl VBA for the analytical part.

Question:
Do you think this is possible? Especially the the option to evaluate either for PN or for DISPOSET? After all you can only assign one primary key?

Right now I'm spending every night reading about databases but I fear it might take a while until i can asses the situation properly - meanwhile I would like to tell my superiours what is THEORETICALLY doable, regardless of if I will be able to. After all I never said that I could create/work with databases.

Thank you so much!

[ranman256]

yes, you can make a db out of this. I would separate compoents into its own db and table just so the 1M records do not overfill the main db.
I had 1 table with 3 million records I kept outside of the main db so it would not slow it down.

The db version would help a great deal with ordering and billing.

[Accessnoob]

@ ranman: Thank you! The data base version? The tool I will use is Access 2010 (though 64 bit - no 32 bit as my profile is stating) , Operating System Windows 7 Enterprise (if that matters).

[RayMilhon]

Read up on Normalization. Look through the spreadsheets with normalization in mind and then create your tables.

[Accessnoob]

@RayMilhon Yes, that is what I've been doing yesterday and today whenever there is time. However I'm struggling with the complexity. In the online examples its always super obvious how to normalize and however in reality it feels impossible. Also there is no way I can achieve anything more than NF1 in Excel without making it borderline impossible to update the whole thing. Thus I'm now looking into making sure alle exports are in 1NF and then this night I hope that i can find some guide to doing the rest in access. I really dont want to use the Table analyzer because that doesnt help with the (necessary) understanding.

[Micron]

Most of the time, spreadsheet data makes for a poor foundation for relational db's. We often tell spreadsheet minded people to forget what they know about Excel when it comes to designing a db so that it doesn't cloud their thinking. I gotta say, the nature of your business is such that I cannot help but marvel that they would task someone with your level of relational db knowledge to do this, and I don't mean that in a disparaging way. I'm saying it sounds like an enormous task for someone who says they don't quite get the concept of table relationships. I guess the best advice we can give is to read up as much as possible and make notes as you go, but the key thing is that if you don't understand what you're trying to learn, seek out another source since that "instructor" is not a good fit for you. That shouldn't be too hard as there must be thousands of sources for just about any db topic you can think of. I'll post some links that may help, or may not work for you. First, though, I question the wisdom of having your back end (tables) as an Access database, given the number of records you have now, which it seems will only get larger. While you could split these into "libraries" of data, to me, it just adds a level of complexity that you don't need. If keeping decision supporting documents (such as pdf charts) or validations (quality system types of documents such as SOP's, checksheets, etc.) as attachments in the db will be a requirement, then AFAIC, Access is a non-starter for your back end tables. You need a more robust database back end, which might require a db admin person who's proficient in SQL Server types of db's. Maybe you have that now, in which case I'd consult with that person. When it comes to fleshing out the design, I still figure there's no better substitute for large paper (like flip chart) and pencil so that you can diagram stuff, rough out forms, plan tables, etc. Anyway, here's what I have for links:
Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html
About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[RayMilhon]

Agree with Micron. that's why I said go through your spreadsheets once you understand Normalization. One thing I've done in the past is open a new worksheet Each Column in the new worksheet list the Columns from a specific worksheet. Then go to the next worksheet and do the same So that in the end you have a worksheet that has a column of the headers for every worksheet Something like

Customer sales Inventory
customerID Item #
CustomerName Item Cost
Item# Item Sales Price
ItemSales Pric Item Desc
ItemDesc
SalesDate

For this structure I would create 3 tables
1 for Customers
CustomerID
CustomerName

1 For Inventory
Item#
ItemCost
ItemSalesPrice
ItemDesc

and 1 for Sales
SalesID
SalesDate
CustomerID
InventoryID


The key is that each actual data Element Exists in 1 location in the database
CustomerName
ItemCost
ItemSalesPrice
ItemDesc
SalesDate

The rest of the fields are Primary or Foreign Keys to link the data together so you can tell who bought what when and the cost of the item sold and the sales price.

[Accessnoob]

Thank you both so much! I myself was also very surprised (that they thrust me with this task) but then again if I succeed it they profit greatly and if I cant do it they only 'wasted' an intern salary which is not substantial to them... (i guess that's their logic).
I've read up on normalization (and still learning though) however I'm fairly certain that I will not completly normalize the table but will probably stop at the 3'rd NF. While I'm fully aware that this might create problems in the long run it but at the same time it allows me to have a database that at least partially reflects the reality and I'm hoping that that will allow me to keep the data import fairly simple. Below is a picture of my current approach. Though of course that will still need to be refined. I've just opened another tread which I probably shouldn't have done since it is still related to this in which I'm trying to get an answer concerning whether or not it will be possible to get time frame & pn summarized reports from this database structure. The link to that thread is: https://www.accessforums.net/showthread.php?t=70791
Thank you again!

[orange]

accessnoob,
Just to say I agree with the others -database concepts and principles are critical to getting a well designed and maintainable database. Also, it seems a massive load to put on someone without more appreciation and experience with database. It also seems to me that there is a certain degree of risk for the company if this is a key factor to the business.

Good luck with your project.

[ssanfu]

Accessnoob,

Here is more reading for you:
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers


AutoNumber
----------------
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.



Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.


Good luck with your project........

[Micron]

Looks like you have given this a lot of thought. Unfortunately the jargon isn't familiar to me, so I can't comment on the viability of the relationships too much. About the only meaningful comments I can make is,
- don't name your fields as they are in the diagram as noted by ssanfu (M.O.N.)
- I don't get the 4 tables with only primary and/or foreign keys in them. They seem incomplete and I can't imagine how they'd be useful, but then again, the business at hand is foreign. Speaking of which, I Googled disposet. It doesn't seem to be an English word, so what does it mean?