NexGen: Aircraft Data Base (Universal)

11 Aug 2020

The question is how can you put ALL the parts into one database? And then make some sense of the thing. I designed my database similar to the one we used at Interdata in the late '70s. So why do I call this universal?

Well, I have separated parts by a Category and given each Category a unique number for its ID and a Name.

So now you have a table Categorys (CatID int, CatName). It really does not need anything else.

The next table will hold the Manufactures' Names, CAGE code, and IDs.

The next table must describe the parts: Info( Cat1D, ManID, InfoID, Type, Part, NSN, Description) where the column ManID is configured to Auto Increment.

The next group of tables is CATxx which holds each Category list of parts.

CATxxx (CATxx int, InfoID int auto increment, MLID int, Rev int). The MLID column is reserved and is Manufacturing Level.

Lastly, we need to track serial numbers, and this will be the last table.

Serials(CATxx int, CatID int, MLID int, Rev int, Serial varchar (20))

So how does this work in real life?

Well here it is:

~~Cris

afterthought: here is an example of the Seeed Studio Network Sheild. As there are two of them with two different manufacturers part numbers. So who is on first? The 2 part numbers would be as follows:

35-1-M0-R0
35-1-M1-R0

using this method there is no doubt which to use.

Categorys
ID Number	Description
19	TTL, Digital
35	PCB, Assembled
50	Avionics

Info
CatID	ManID	InfoID^*	Type	Part	NSN	Description
19	1	1		74LS138N		Decoder 3:7
50	2	2	JB678	N1234-L02		Instrument, Fan Speed, N1
19	1	3		74LS373N		Octal Transparent Latch
35	3	4				Ethernet Shield V1.0
35	3	5		W5500		Ethernet Shield V2.0

Note: Column marked with a * are auto-increment.

CAT19
CatNum	Base*	MLID	REV	InfoID
19	1	0	0	0
19	2	0	0	3

CAT35
CatNum	Base*	MLID	REV	InfoID
35	1	0	0	4
35	1	1	0	5

CAT50
CatNum	Base^*	MLID	REV	InfoID
50	1	0	0	3

Serials
CatNum	Base	MLID	REV	SN
50	1	0	0	G-123
50	1	0	0	G-234

Manufacture

ManID^*

CAGE

ManName

Texas Instruments

General Electric, Aircraft Instrument Division

Seeed Studio