500Eboard VIN Database: The Second Generation

[gerryvz]

I alluded recently to a new phase for the VIN Database. Wanted to detail my plans for it.

It’s not going away. Rather, it is being integrated directly into this site and will be fully searchable, harnessing the power of the database and the XenForo custom development capabilities (which I’ve never used before).

I have created a private, development-only server at dev.500Eboard.com which is completely separate from this server. This server will be used exclusively for creating the second-generation VIN Database. This will include all aspects of the project.

Then when it is ready, it will be brought over and added to this server.

We will start off modestly, creating the new VIN Database using ONLY the US 1992 data. Once that is up and running, then we will bring in 1993, then 1994, then Canada, and then rest of world.

For the first time, I am developing a custom “plug in” that will provide much of this functionality, in terms of importing data on the cars and associating it with the right data structure / data model.

For Phase 1, we will NOT have all of the historical notes associated with each car, photos, and so forth. That will be brought in after Phase 1 is complete.

This is moving from doing code tweaks (as I’ve done for many years on an as-needed basis) to full-blown software development, creating a true plug-in module, using GitHub, and so on.

I’ve prototyped a front end page design here in XenForo for displaying data and info for individual cars, and this is what it will look like :



The existing VIN Database Google Sheet will remain up and running, as is, for the foreseeable future. But when the 1992 US import happens, we’ll freeze that tab, then 1993, then 1994 etc.

Once that is done, you will easily be able to do things never before possible. For example, if you wanted to know how many cars (locally or Canadian or 1992 US) had a given option code, or combination of codes, this can easily be done. Same thing if you wanted to call up how many Beryl cars globally had IRCL as an option. The possibilities will be endless with the new data structure.

The good thing is that this isn’t a major lift, in terms of the data. We have all of the data in hand — it’s just sitting static in a spreadsheet right now. This project will make the data come alive — you will be able to directly interact with it !!

This is an exciting project and it will be nice to have all of the data — and the ability to query and make the data much more usable, directly, here within XenForo.

I will continue to keep everyone informed on this thread along the way. This is going to be an ongoing project that will likely take a couple or several months, depending on other priorities in my life.

Right now I’m putting some significant effort into the 126board, which I’ve largely neglected since bringing it back online about 15-16 months ago. It deserve some love and attention.

 

[gerryvz]

The development server is now live. It is password-protected, so there is no way that anyone can reach it unless they know the login and password, but it is fully operational.



Here is the main screen and the control panel (similar to this forum's control panel) to show that's an operational web server and forum



Development of the VIN Database Registry software add-on is now underway. Its name will be "Eboard/Registry".

"Eboard" will be the brand name (essentially the software company) and "Registry" will be the product/software (add-on) name.

Here is some of the early code, under development.




Our first database table is created !!!

 

[gerryvz]

.....and it's ALIVE !!!




And, now populated with a Test 500E....



And when you click on the Registry # (which is a link), you get this page.....our proof-of-concept individual detail page....

 

[gerryvz]

Updated version of the individual car display page. Incorporating more complete information, including option codes....

This is by no means near final, but you can see how it is coming along.

We will begin populating with 1992 US car data probably tomorrow.




Slightly updated version. We are getting closer to the page design that I posted last night.




Major accomplishment today is that we have connected the database to the display page, so that individual cars can be called up.

Next step is loading the 1992 US cars into the database. We will be using existing VIN Database information, and cross-referencing that with LastVIN data cards for every car. We will use Alteryx (my company's software, which is made for doing exactly this), to read the PDFs, read the VIN Database data, compare the two, join, prepare and scrub the data to make everything fit into the Registry schema.

Anomalies (differences in the data) will be flagged and will be manually investigated.

The technical infrastructure is now 100% in place for the VIN Registry, and it is tested and working 100%.

 

[gerryvz]

Today I was focused on merging, preparing and transforming the 1992 US data from the VIN Database, and preparing to merge it with the LastVIN data cards for each VIN.

We are doing this in Alteryx Designer, which is the flagship software product of my employer. Designer is tailor-made to perform exactly these types of tasks, including reading all of the text off of each LastVIN data card and transforming it into an individual text record. Then the LastVIN text record is combined with the VIN Database data, and all fields compared and overlapping data fields are merged together,

There are dozens of individual operations that go into merging, parsing, examining coordinating, joining all of this data.

This is powerful stuff.

Here are some views of the very complex Alteryx workflow that I created to work with all of this data from these very different sources.


The combination of these data sources will make for the very rich record for every individual car. Then we will extend this to the 1993 and 1994 model years.

 

[gerryvz]

Right now, I've got about 20 hours into this project over the past four days.

I'm happy to report that all 635 US 1992 cars are now fully merged, with all associated data attributes and option codes attached, and loaded into the development database. This required more than 250 individual steps to accomplish, and it is 100% successful.

These are the first 10 records (of 635) for the US 1992 cars, shown actually imported into the database.


The database has two distinct tables -- one for the cars themselves with key data (VIN, FIN, engine number, trans number, date produced, interior and exterior colors/descriptions, and so forth), and the second table specifically for the option codes associated with each individual car. This option table contains nearly 12,500 records covering all 635 cars. The first table is just 635 records - one for each car.

I put a LOT of thought into the data architecture, given the attributes associated with each car (43 data fields for each car). And then I created the database schema to best reflect this. Separating the individual cars from the options provides SUPREME flexibility as far as being able to search for individual, or combinations, of attributes.

And this is where "The Next Generation" of the VIN Database comes in. It won't be just a static VIN-based listing with the colors, and numbers associated with each car. It will also have all option codes for each car, and these can be SEARCHED from within XenForo.

I don't think anything like this has ever been created for any other Mercedes-Benz model. At least, I haven't seen it.

We can largely re-use the same workflow for the 1993, 1994, Canadian and RoW cars, so the process for adding these should go MUCH faster.

Once we get the 1993s and 1994s added, I'll then provide some screen shots from the XenForo instance on the Dev server as to how the database can be queried by any of the 43 data attributes associated with each car - individually or in combination.

The main thing I have to do now, is to download each of the remaining LastVIN data cards for each of the 1993 and 1994 cars. It's going to take some hours to do this manually / repetitively. Fortuantely there are somewhat fewer data cards for the 1993s than 1992, and even fewer for 1994 (and I have SOME data cards -- courtesy of @Otrebla49 -- for the 1993s and 1994s already.

More soon, but we hit a major milestone tonight on the project.

 

[gerryvz]

If you guys are interested, here is a display of the primary attributes that each 500E/E500 will have, in the database. This DOES NOT include each car's complement of individual option codes (which will be relatively similar for US and Canadian cars, but will vary wildly for non-US cars).


You can see the individual attributes that we have assigned to each car:

1. Vehicle ID (this is a proprietary number that the database will use to track each car in the hierarchy)
2. VIN (for US/Canadian cars)
3. FIN (for ALL cars, including US/Canadian)
4. Production order (where each cars sits in the 10,479 production sequence)
5. Model year
6. Market
7. Country (yes, we maintain separate fields for the car's market, and for the car's country)
8. Exterior paint code (a three-digit number)
9. Exterior paint description ("i.e. "Mother of Pearl Gray Metallic")
10. Interior color code (a three digit number)
11. Interior color description
12. Status (whether parted, wrecked, odo rollback, etc.)

And so on, and so forth.
So, every possible piece of data for every car, is recorded (if available) and tracked. And this will include the historical notes, where available, in the VIN database).

The option codes add another whole layer of data on top of this. Each car can have up to say 20-30 option codes, depending on how it was ordered. It will be interesting to see which car has the most option codes -- we will be able to determine this very soon.

Anyhoo, I wanted to give you guys a glimpse into how serious of an effort this is.

Honestly, this is the fulfillment of a dream I have had for more than 20 years. I think it's really going to take this site on a very different trajectory in the future. The forum will, of course, remain the centerpiece of the site, and will be unchanged. But the information that the forum contains, will be packaged and presented in a very very different way. When I finish this VIN Database TNG project, I'll detail out for you what my plans are.

 

[doolar]

This is so frikkin cool Gerry!

 

[gerryvz]

Here are ACTUAL cars from 1992, US models (all 635 -- this is just one screenful) loaded into XenForo.



When I click on the first car, this is the "detail" page that I get.



These pages are going to be radically redesigned, per the first post in this thread. They will look very different and be MUCH more attractive in the future, but that will be the last phase of the project.

But this now confirms that everything is working. THE DATABASE AND DETAILS FOR EACH CAR, ARE NOW INTEGRATED AND CAN BE DISPLAYED WITHIN XENFORO.

Now I've updated the above detail screen, with the more explicit paint and interior color names, as well as adding the build/delivered by date fields.


This is a BIG. FREAKING. DEAL. The custom software add-on to XenForo that I have begun creating, is working.

 

[gerryvz]

Adding Search capability now.....



Here is a search of the database (1992 US cars only) for color code 441:



Testing each search field .... SUCCESS.....Search is working GREAT !!

 

[gerryvz]

Now let's do a COMBINED search on TWO factors.

Let's search for all US 1992 models with 199 exterior + 271 interior:




WOWZO !!! 230 cars !!



Let me summarize what I've just shown you:

We now have:

• 635 1992 U.S. cars loaded into XenForo
• 12,831 vehicle-option relationships across 46 option codes
• individual vehicle pages with factory data/options
• normalized paint and interior names
• and working searches by VIN/partial VIN, Registry number, paint, interior, and combinations of those fields

 

[Bogeyman]

Is this copyrighted/protected?

 

[gerryvz]

Is this copyrighted/protected?

It's my intellectual property -- 100% stem to stern. Data and all code.

 

[gerryvz]

1993 cars are now loaded into the database and available. It only took a couple of hours to process everything, re-using the Alteryx workflow I developed over the past couple of days. Had to modify a few variables, but no big deal. Most of the time was quality-checking the data and making sure everything lined up correctly.

Couple of screen shots showing 1993 data, as well as a search for 888 paint code "Beryl" cars, showing both 1992 and 1993 cars.



Here's a drilldown on a 1993 car, just to show they are now part of the database:



And one more cool thing: All notes associated with each individual car (previous sales, condition, etc.) are now a part of each car's individual record:



Oh yeah, I updated things so that the car display shows 50 cars per screen. You can also click on each heading (column) and it will toggle between ascending or descending display.

More to come....1994 US cars should be done and integrated some time tomorrow night......I may have time to get to Canadian cars too....

 

[gerryvz]

Doing some tweaking now so that vehicle notes display more legibly. Not quite there yet, but you see what I'm trying to do....

Old Notes block



New Notes block (not final, still have some tweaks.....)

 

[gerryvz]

All 1,528 cars have been now added and are complete.

I have also added the 49 Canadian 1992 model year cars. So this brings the total up to 1,577 cars in total.



Here's a search/select of JUST the Canadian cars, to show them isolated (note "Canada" is in the Market search box):



Here's the drilldown on a single, Canadian car:



1993 and 1994 Canadian cars are next to be added, today. It took A LOT of tweaking to get those Canadian cars added successfully and fully verified, but we should be able to use the same workflow for the 1993 and 1994 cars (not to mention there are far fewer of them; only 85 cars were officially imported to Canada, and 1992 was 49 of those 85 cars).

 

[gerryvz]

All Canadian cars are now loaded and available in the database. The lone 009 (Sapphire Black) Canadian market car caused me a lot of heartburn, but in actuality it was a godsend, because we will be using the 009 color string / number in future "ROW" import of the remaining ~9000 cars.

Total records now on the server (all US + Canada cars from 1992-1994)


US cars (1992-1994)


Canadian cars (1992-1994)



That said, the only thing keeping the import of the remaining ROW cars is the lack of the LastVIN data cards (PDF format) containing the option code info, and which also coordinate / confirm the existing VIN Database information that I have compiled on the Google Sheet.

I only have around 606 ROW PDF data cards from LastVIN .... so I need to get the remaining ones. That's a tedious process, especially for 8,500 individual PDFs to compile. Just for 1994 it took around 1.5 hours to download ~425 or so.......

So perhaps we can crowdsource this duty among 10-12 people to download say 500 or 1,000 VIN PDFs per person.

 

[Type5314]

Astonishing skill level and attention to detail. Simply splendid!

 

[gerryvz]

Here are a few representative pages from the database. I will now begin designing and implementing the "front end" pages that will display everything in an attractive way.

I have also decided to provide searches of up to FOUR option codes at a time (either separately or together with other parameters such as country, or paint code, etc.).

This means that users will be able to search for combinations of options -- for example, how many Smoke Silver cars were delivered to Switzerland with IRCL, sunroof, front seat heaters and rear sun shade ?

This is ground-breaking stuff. It really is. The power of a relational database is nearly infinite.......

 

[doolar]

I’ll happily help with the LastVIN extraction, just let me know!

 

[gerryvz]

I have also decided to provide searches of up to FOUR option codes at a time (either separately or together with other parameters such as country, or paint code, etc.).

This means that users will be able to search for combinations of options -- for example, how many Smoke Silver cars were delivered to Switzerland with IRCL, sunroof, front seat heaters and rear sun shade ?

You can see that we now have FOUR option search fields, for combining searches of up to four individual options.





Here's a four-way, additive option code search:

 

[gsxr]

Gerry, the Canadian 009 car also had code 958, correct? That would make four total Limiteds imported to North America.

 

[gerryvz]

Now creating drop-down menus for some search fields, to make it easier for folks to specify (and combine) search parameters.

Here are a couple of examples:

 

[gerryvz]

Gerry, the Canadian 009 car also had code 958, correct? That would make four total Limiteds imported to North America.

Yes, and the screen shot from the database above confirms that. Look and see that there is one Canadian car, specified with code 958.

Do you see 958 in the fourth option search box?

 

[g300d]

Gerry, I believe your creation is an invaluable resource to all levels of the 124.036 community of owners, enthusiasts, and even people who earn a living off of these cars.

I and I'm sure everyone else familiar with it (well, almost everyone) appreciate your effort creating it and making it available, at your own cost, including creating a public discussion forum where like-minded people can discuss it and most anything about it.

Thank you, the forum team, and everyone contributing their knowledge and experience.

 

[gsxr]

Ooops, I missed the screenshot! Wonder how that Canuck Limited was ordered?

 

[gerryvz]

Gerry, the Canadian 009 car also had code 958, correct? That would make four total Limiteds imported to North America.

I now also have the ability to search on 958 as a drop-down. It identifies the same four US and Canadian cars. Of course, you can filter this further by country (US or Canada) .... and when the RoW data cards are integrated, by other countries as well.

And yes, those stray paint codes 904 and 888 have been filtered out in the current iteration.....

 

[gerryvz]

Ooops, I missed the screenshot! Wonder how that Canuck Limited was ordered?

Also notice that it DOES NOT have a US-style "WDBEA36" VIN -- it has a FIN only.

 

[gsxr]

Also notice that it DOES NOT have a US-style "WDBEA36" VIN -- it has a FIN only.

My EPC, and LastVIN, both show a North American VIN for that chassis, WDBEA36E9RC144809.

Where are you seeing only a FIN?

 

[gerryvz]

interesting ... it's been reversed. I need to find out why. I haven't seen that with any other car.

 

[gerryvz]

A simple database query shows the problem ... it's endemic specifically to the import of the 20 1994 cars ... looks like the mapping was switched at the workflow output in Alteryx.....

Houston....we have a slight problem...but it will be easy enough to fix....




We can fix it directly in the database. Looks like all of the Canadian cars are also showing WDBEA36 for their FINs too.

 

[gerryvz]

I went back and redid all of the Canadian 1992, 1993 and 1994 workflows - rather than a band-aid in the database. It was the right thing to do.

I'll upload everything in the morning and it will all be updated and correct.

All the US cars were correct from the get-go. Thankfully.

 

[gerryvz]

I hope all of these updates are not “Too Much Information” !! It’s just I’m excited about how the project is coming along.

 

[gerryvz]

My EPC, and LastVIN, both show a North American VIN for that chassis, WDBEA36E9RC144809.

Where are you seeing only a FIN?


View attachment 241228 View attachment 241229

It’s also weird because the screenshot above showing the four 958 cars did show the VIN just fine. It’s only the detail page where things were reversed.

Oh well. The problem is fixed now anyway. I just have to load the files on the server into the database.

This is by two orders of magnitude the hardest project I’ve ever pursued. I’m coding in SQL, php AND html literally interchangeably depending on the files that need to be modified.

Just adding a simple dropdown requires new code in php and HTML in different files.

My brain is aching because I put 10 hours in on this, just today.

 

[gerryvz]

I’ll happily help with the LastVIN extraction, just let me know!

That would be splendid. If you go into the VIN Database and go to the “Production Order” tab, the first 1,000 VINs (#1-1000) would be a great start.

I will take 1001-1999.

Go to LastVIN.com. Paste a FIN into thr blank and enter, and when the data card comes up select the “Save PDF” at the top of the screen.

Each PDF is 51-52K in size. If you can make available via Dropbox or Google Drive that would be ideal.

I typically open 100 blank lastvin tabs at a time on a browser window, cut and paste the VIN into each one, go to the next one. Then I come back and hit “PDF” and the browser downloads to the hard drive. Then CTRL-W to close the tab and move to the next one.

I can download about 500 PDFs this way in about an hour or 90 mins. It’s tedious but it works.

Would love to have others help, as many hands make light work. And it means the ROW Portion of the site comes online much faster.

 

[gerryvz]

I went back and redid all of the Canadian 1992, 1993 and 1994 workflows - rather than a band-aid in the database. It was the right thing to do.

I'll upload everything in the morning and it will all be updated and correct.

All the US cars were correct from the get-go. Thankfully.

New Canadian 1992, 1993 and 1994 registry files loaded into the database.

MariaDB integrity check confirms proper VIN and FIN presence for each record:



And checking a representative car from each Canadian model year shows everything seems to be in good nick.....

 

[doolar]

That would be splendid. If you go into the VIN Database and go to the “Production Order” tab, the first 1,000 VINs (#1-1000) would be a great start.

I will take 1001-1999.

Go to LastVIN.com. Paste a FIN into thr blank and enter, and when the data card comes up select the “Save PDF” at the top of the screen.

Each PDF is 51-52K in size. If you can make available via Dropbox or Google Drive that would be ideal.

I typically open 100 blank lastvin tabs at a time on a browser window, cut and paste the VIN into each one, go to the next one. Then I come back and hit “PDF” and the browser downloads to the hard drive. Then CTRL-W to close the tab and move to the next one.

I can download about 500 PDFs this way in about an hour or 90 mins. It’s tedious but it works.

Would love to have others help, as many hands make light work. And it means the ROW Portion of the site comes online much faster.

I don't know if I'm doing something wrong, but I can't copy and paste from the spreadsheet

 

[gerryvz]

I don't know if I'm doing something wrong, but I can't copy and paste from the spreadsheet

Please try again.....

 

[gerryvz]

Tonight I did a lot of development work on the "user interface" for displaying individual cars.

I have also integrated every individual car's page with the LastVIN PDF data cards already on the server.

Here is the display page for my own car. There will be additional changes and such, but this is the key information that will be displayed for each car.




This is all the result of A LOT of tweaking in HTML / CSS and php to get this display CLOSER to what I have been envisioning....



Next up will be the mechanism so that information on individual cars can be changed/modified/added from the user interface. And of course more tweaks to the page to make it more attractive.

I'll also be implementing a "landing page" for search capabilities. This will be the "front end" or "front door" of the Registry portion of the site.

Today I finished implementing the drop-down menus for the search box. More colors and options will self-populate when the non-North American cars are imported into the database in the future, but at least you see what is available now.

 

[gerryvz]

A few enhancements this morning so far:

I have "expanded" the VIN search box to also include FINs (and of course last six digits of any car):



Separately, I have now imported all URLs from the Google Sheet, and all links associated with any car (even multiple links) are now assigned to that car, in the "Links" box. This allows you to click through to specific XenForo forum threads about a given car, from that car's individual page.

It also correctly classifies a given link as a 500Eboard link, or an auction link, or a dealer link, and so forth.

 

[gerryvz]

Now I'm starting to build out the VIN Database landing page.....

 

[gerryvz]

Landing page is coming along..... currently building out the boxes at the bottom to display stats and such.

Have to create some new workflows in Alteryx to do this right.




Unless I can get some help with the RoW LastVIN data card PDFs, this is going to be a splendid US + Canada only VIN Database !!!!

 

[gerryvz]

More pages added (bringing in data from the Google Sheets) to support the tiles on the main Landing Page:



Here's the Landing Page now -- the main central VIN/FIN/partial search is 100% functional:

 

[gerryvz]

The pages for the site themselves are pretty much now done. Meaning that we're fairly close to being able to bring everything over from the dev server to this server.

Two things remain -- first, making some of the fields "editable" so that Notes about cars can be added, records corrected or changed (if needed), new URLs (say for a new HaT auction) can be added, and so forth - directly from the user interface of the Registry display pages.

The editing capability is nearly complete.

Second -- final development of the XenForo "add-on" that actually enables this Registry to exist and integrate with the forum.

The add-on developing is coming along ... we're at version 1.0.0 Alpha 2. We'll be going into "beta" versions later today, for final testing.



If all goes well, we should be going into production with the integrated Registry this week, or this coming weekend at the latest.

I implemented the dev server about 7 days ago. It's been an intense week of software development.

 

[gerryvz]

I had to build the "permissions" capability into the XenForo Admin Control panel so that individual users (or groups of users) are enabled to edit the Registry.



Once that was done, and permissions assigned to the user "gerry" (I am the only member of this "Development" forum, not to mention the Administrator), then I checked to see if the editing capability was exposed. See the little "edit" button?



Using my own car as a guinea pig....





Editing is complete and working !!!

Now I need to extend it to the "URL" portion, so that new URLs can be added (say, if a car comes up for auction or is found on a flipper-dealer's web site.....or a new thread is created on 500Eboard about the car.

 

[gerryvz]

OK -- I know the ongoing updates are probably starting to grate on people. You're likely thinking "Just deploy the damned Registry, already!!!"

So here's a progress report and inventory on where we basically are on the road to you guys getting an UNPRECEDENTED registry resource as part of this site.

First of all, we're about 80-85% complete with the entire process.

Here's a breakdown on all of the key project elements, status of that element, and my estimation on how complete we are on that piece.

Project Element	Status	Estimated completion
Database architecture & schema	Essentially complete	95%
US Vehicle Data	Complete & validated	100%
Canadian Vehicle Data	Complete & validated	100%
Options & Option relationships	Working	95%
Paint & Interior reference data	Working	95%
Vehicle detail pages	Working & mostly polished	95%
Registry search, browsing, filtering	Working	90%
Registry landing page	Working & mostly polished	95%
Production statistics	Working	90%
Worldwide reference statistics	Working	90%
Images	Infrastructure & image display working	85%
Registry notes editing	Working + audit history	95%
Related links management	Add, edit, delete working	90%
Permissions	Working	90%
Source document management UI	Not yet built	40%
Production deployment & migration	Not yet done	20%
Final QA, security & permissions testing	Still needed	50%

Overall, I'd estimate around 15-20 more hours of focused work, and then we can do the cutover.

I estimate the actual cutover of all of this to this server will require 2-3 hours. During this process, the forum will likely be unavailable for 30-60 minutes. I will let folks know in advance when this window will be, but it will likely be on Sunday morning (EDT).

Cheers,
Gerry

 

[sheward]

For me, the resident Luddite, all the work shows me how little I know about I.T. Looking forward to this exciting addition to the board.

Thank You for all the work.

 

[gerryvz]

As I've mentioned, over the past 10-11 days I've been working pretty intensively on the next generation of the 500Eboard VIN Registry.

We've now completed the initial U.S. and Canadian database, covering 1,613 individual 500E/E500s, and built the new Registry directly into a full "Development" forum. A lot of the recent work has been the less-visible but important stuff: cleaning and validating the underlying data, building the database and search capabilities, creating individual pages for each car, adding production statistics, photos, source documents and historical links, and building editing and revision-history tools so the Registry can continue to improve over time.

We've also spent quite a bit of time testing the installation process on a separate copy of the forum rather than experimenting on the live site. My best estimate is that I've put roughly 100 hours into the Registry project so far, including the original data preparation and validation work, database development, XenForo integration, interface development, testing and troubleshooting.

The good news is that we've now reached the point where the new system is essentially ready to go live.

I'm currently planning the production cutover for TONIGHT, probably beginning around 7–8 PM EDT. Before making any changes, I'll take a complete backup of the server as well as the database and forum files, and then temporarily close the forum while the new Registry software and its data are installed.

We've already rehearsed this process successfully on a separate test installation, so I'm expecting the actual outage to be fairly short — probably around 15–30 minutes, although I'm reserving up to an hour just in case. Once everything is installed, I'll verify the vehicle counts and data, test searches and individual vehicle pages, check the editing and other Registry functions, and then reopen the forum.

Assuming those final checks go as expected, the new 500Eboard Registry will be live tonight, giving you a searchable and expandable resource that we will continue building out with additional vehicle histories, photos, documentation and, eventually, the rest of the worldwide 500E/E500 production.

I'm looking forward to getting all of you this new capability, that I have worked exceedingly hard on, enabled later today !!

 

[gerryvz]

As I've mentioned, over the past 10-11 days I've been working pretty intensively on the next generation of the 500Eboard VIN Registry.

We've now completed the initial U.S. and Canadian database, covering 1,613 individual 500E/E500s, and built the new Registry directly into a full "Development" forum. A lot of the recent work has been the less-visible but important stuff: cleaning and validating the underlying data, building the database and search capabilities, creating individual pages for each car, adding production statistics, photos, source documents and historical links, and building editing and revision-history tools so the Registry can continue to improve over time.

We've also spent quite a bit of time testing the installation process on a separate copy of the forum rather than experimenting on the live site. My best estimate is that I've put roughly 100 hours into the Registry project so far, including the original data preparation and validation work, database development, XenForo integration, interface development, testing and troubleshooting.

The good news is that we've now reached the point where the new system is essentially ready to go live.

I'm currently planning the production cutover for TONIGHT, probably beginning around 7–8 PM EDT. Before making any changes, I'll take a complete backup of the server as well as the database and forum files, and then temporarily close the forum while the new Registry software and its data are installed.

We've already rehearsed this process successfully on a separate test installation, so I'm expecting the actual outage to be fairly short — probably around 15–30 minutes, although I'm reserving up to an hour just in case. Once everything is installed, I'll verify the vehicle counts and data, test searches and individual vehicle pages, check the editing and other Registry functions, and then reopen the forum.

Assuming those final checks go as expected, the new 500Eboard Registry will be live tonight, giving you a searchable and expandable resource that we will continue building out with additional vehicle histories, photos, documentation and, eventually, the rest of the worldwide 500E/E500 production.

I'm looking forward to getting all of you this new capability, that I have worked exceedingly hard on, enabled later today !!

The Registry is now LIVE for everyone.

500Eboard Registry

Here's the Desktop link (RED button):



Here's the mobile view (Registry in the main hamburger menu at top left of screen):



That is a major milestone for the 500Eboard: the new Registry has gone from the original spreadsheet/data-card concept, through Alteryx ETL, a purpose-built MariaDB data model and XenForo add-on, development and QA, to a functioning production system integrated into 500Eboard.

And we got through the actual production deployment without having to roll anything back.

And I have authored my first "official" XenForo add-on as a true software developer....

 

[The Emperor]

The power of a relational database is nearly infinite.......