Topic: Complex Data Model Question (Data Table Layouts)

I am tasked with solving a very complex problem.  I am building an inventory system for an electrical company.  The company has about a dozen categories of products.  Each category has multiple products under it.  Each product has one or more data tables that look like below.

http://carpeaqua.com/images/cat1.png

On some products, there are multiple tables that have multiple layouts (different columns)

http://carpeaqua.com/images/cat2.png

I am at a loss as to how to implement this in my application.  What I am thinking right now is to have a model called Part that has a has_many relationship to another model called PartRows.  PartRows is STI-based with different subclasses with different validation rules (for each of the dozen main categories).

My main issue is how to determine what fields to show for the user after they select their category type and then how to render those tables for the user.

Has anyone implemented something similar to this and can provide some sort of guidance?

Re: Complex Data Model Question (Data Table Layouts)

Those tables do look very difficult to manage with a simple database. If that table content is static, and you just need to display it to the user, you could just have a big text column in the database containing an HTML table element and output that to the browser. This obviously has limitations, it won't be easily extensible for one. But it might be sufficient if you just need to display the info to the user and not do anything else with it.

I doubt this would be sufficient for your needs, but thought I'd throw the idea out there anyway.

Railscasts - Free Ruby on Rails Screencasts

Re: Complex Data Model Question (Data Table Layouts)

If they need to be dynamic, you could possibly create a template for each table type (and a matching input form to go with it)and store your data in the database in a text field something like:

first_table_type_here*row1 col1, row1 col2, row1 col3|row2 col1, row2 col2, row3 col3*
second_table_type_here*row1 col1, row1 col2|row2 col1, row2 col2*

Then you'd have the script split that string up and either display it or populate the forms or, conversely, join the data from the form together to create the string to put into the DB.

It's not pretty, but it might work.

Re: Complex Data Model Question (Data Table Layouts)

Im glad to see that I am not the only one who finds this incredibly complex.  I think the data mapping might work as long as I keep the price in a separate field.