Redesigning a flat file

[barkingsheltie]

I want to redesign a pretty simple cross-reference db that we use here at work. Its a very simple flat file, one table beast at the moment.

A little history will possible shed light on matters:

We make crimping tools for use on electrical contacts that are used in a variety of connectors.

There are over 1,000,000 contact part #'s worldwide - we have identified some 50k numbers, linking them to various tooling we manufacture.

Our simple table has the connector company, part number, size, etc, and then the remaining columns, about 14, which are used to denote our tooling that works with this contact.

For example, say we have contact 'A' - it could be that our 400 series, 550 series, 700 series all have tooling/solutions that work with this contact - our other product groups might not (thus blank fields).

We have about 10 different product groups - some are different in the way they operate - electrical, hydraulic, pneumatic. Some in size -hand held - some half a ton. Some simply in the overall range of wire size they accommodate.

The simple table I have works now. We can do simple searches, finding exact matches. When no exact, search by other attributes. But we would like to do much more...

Our current inventory used day to day exists in a trad client-server order entry accounting system. But this has no provision for doing these searches. Additionally, half our sales calls are from customers who only have the customer contact part # - and it gets tedious looking up in another program - so I'd like to quote from filemaker - usually we are looking up more than one number at a time - and have pricing, and availability. Hence, we need to update our flat file single table immensely.

My question(s):

I would make a connector company table, a connector inventory table, and of course our product inventory table.

Our current setup is convenient when we enter a new contact, e.g., after entering the part #, one just tabs through the various fields, adding our part # when appropriate - we make a 300, 400, 500, 600, 700, 800 series of tools - so lets say contact A would have a 400-11, 600-33, 800-404 entered into related fields.

If I were designing this TODAY, can anyone help guide me what the best methodology with tables and relationships might be?

Overall, though one contact can only have one connector manufacture, it can be linked to more than one of our products. And as stated, any one of our products may be linked to more than one contact. Thus a many-many relationship exists.

Creating another child table (a 'line order' type solution) to get around this - it would seem I would need to create one for each unique group in my product inventory?

Btw, my goal when searching is to employ tabs (we recently upgraded to filemaker pro 9); this will make searching far more convenient - if a search is found, clicking on the tabs will reveal various product groups that match. Though I will try to create a script that will summarize which product groups do have entries.

shelley

[barkingsheltie]

39 views and no replies!

My writeup was probably poorly written - and I don't mean to ask for someone to design this for me - although Im open to anyone who want's to consult for a reasonable fee.

Maybe if someone could point me to material/tips related to complex many to many relationships that my problem *seems* to suggest - I believe I understand the normal parent-child and sub-child methods that are used for instance in invoicing, order entry situations.

Maybe the way to tackle my problem is too split the inventory into sub-groups (child), and then use a join operation to assemble the full array.

I'm more versed in command line c programming - and I know how I would handle these types of operations here.

shelley

[Genx]

It's not that lol... long posts scare us - to much effort :

1) Company Table

2) Products / Tools Table

3) Parts Table

4) Products - Parts Join Table

[The Shadow]

I agree with Genx, and have provided an example file showing how this might be setup.

I also added a ProductGroups table, so that those traits (hydralic, etc), are also factored out into their own table.

I realize its not pretty, but I think it should demonstrate what direction to head in.

CrimpTools.fp7.zip

[barkingsheltie]

I agree with Genx, and have provided an example file showing how this might be setup.

I also added a ProductGroups table, so that those traits (hydralic, etc), are also factored out into their own table.

I realize its not pretty, but I think it should demonstrate what direction to head in.

Yep - that helps alot. Thanks. I will tinker with this some over the weekend and on monday - the file tells me how I was either missing how to normalize my tables and expand the main table into appropriate child tables. Thanks :thumbup: