Yet another relationship question :)

[barkingsheltie]

Possibly. Always hard to know exactly which forum to post such.

Im looking to get insight/impressions by others.

Not a newbie, but not an expert like a bunch of smart folks here.

Im updating a database we use to keep track of serial numbers for products we sell, and in addition we receive some of these products back for service. Its useful to keep track of when/how many times a product has been serviced. The service database is separate from the serial database.

Right now, we have separate tables to track each different product serial number - for example, widget A starts with serial # 1,..., widget B starts with serial # 1,...

In retrospect, we should have used a different schema for serial numbers, like A1, A2 (for widget A) and so on. But I digress!

Having separate tables is cumbersome, although I just fake it in layout (using faux tabs for each model family).

I create a related record in the services table in the appropriate serial number layout though a portal using "create related record".

I can do the reverse in the services database, but I have to use a script to generate a record in the appropriate table. If there was just one table for serial numbers, seems like it would be easier to handle this.

I am thinking of consolidating all records (serial numbers) into one table - I would have different fields for the various serial numbers per product. Perhaps that is the deal breaker that dictates these should be separated out?

When I generate a new record, I would auto enter the date, and after the product model type is selected (we have 400, 500, 600, 700 and so on model type families), I am thinking of using a calculated field with a case function to generate the appropriate serial number. I suppose I still might have to have separate tables so I could go out and get the last record in order to increment the serial number.

Maybe this is lame, but wondering if consolidating in this vein provides advantages? Seems to me that being able to look at one table from the services layout/table would be cool - easier to sort which products are open, filled/shipped, filter by model type, etc, without having to use join tables.

This might be more of a design question, and I apologize if its posted in the wrong forum.

thanks,

shelley

[Matthew F]

I would vote for consolidating your tables. What's the point in duplicating the same data types in multiple tables? It seems like the only functionality that you're gaining is the ability to auto-increment your serial numbers. However, you could use a self-join relationship and a relationship to calculate the next serial number as they are being created. The match field would be product type, and the table would be sorted by serial num (descending order). The next serial number would then be:

SelfJoinTable::SerialNum + 1.

Also you are going to want to combine your product serial numbers with a prefix into a calculated fieldthat defines the product so you can keep them straight. Like you said A1, A2, A3, B1, B2, B3, etc. You could then use this as the primary identifier for each record.

[barkingsheltie]

Thanks!

If I understand you right, within the consolidated table, when the user requests new record, they could be queried for model type for instance, and then the script could go to a TO (this would be the selfjoin you mention) and filter only those records for that family (match field), and after sort, descending, voila, the necessary data for incrementing.

You would have x # of selfjoins (TO) as you have families.

If I understand you right, that is perfect, thanks for helping me.

shelley

[comment]

Is it important for the serial numbers to be consecutive within each product (I mean from now on)?

[barkingsheltie]

Prob not...hmmmm.

However, quite a few records and thus overlap with previous instances. 20k records for greatest sold product, 10k for next, etc.

One reason there wasn't more differentiation with serial numbers, is these are physically stamped onto cast aluminum body of the products, and its time consuming, tedious, so long numbers were discouraged.

[Matthew F]

You could a prefix or suffix to the old serial number to keep them separate in your database.

[comment]

I would suggest you maintain a single serial number sequence for all products, with a prefix to identify the product type, e.g.:

A101

B102

B103

C104

A105

...

Managing separate serial sequences within the same table is rather tricky; it is quite easy for two users creating a new product at roughly the same time to end up with a duplicate serial number.

long numbers were discouraged

Long numbers can be reduced to short strings by raising the base. For example, 123456789 can be shortened to "75BCD15" (hexadecimal).

[barkingsheltie]

Thanks mfero and comment.

Asked my boss, he thinks that's great. The person that stamps them is confused, but I think when he sees the database in action, it will be fairly straightforward.

I like just using the A, B, Suffix, as it will be easier for searching. Im sure you could easily get around with script, formatting, but just seems easier to me to add suffix.

Thanks again for the quick and helpful suggestions.

[barkingsheltie]

One last,

Would it be worthwhile to use an interface layout, 1 record, as opposed to doing everything on the main table, e.g., user could fill in customer, model type, date, and then initiate new serial # script which would grab relevant info and then commit record to new table? Like most of the multi-user scenarios I've seen.

I like that, in the event some hiccup occurs, and seems to me the id for the main table should be the serial number sequence, without the suffix of course.

[Matthew F]

I agree with Comments suggestions from a database design standpoint. However, there are lots of ways to design a database and your issues about implementation are equally important. We have similar issues where workers issue separate product serial numbers because it would otherwise require continuous communication with the database or amongst workers to determine the next serial number, and they would no longer be able to use consecutive numbers. Adding a worker specific prefix solves the problem of uniqueness.

Whether you use the suffix as your main record identifier will depend on whether the serial numbers are unique or not. If you use unique serial numbers as comment suggested then they can serve as the primary ID. If you continue in your current practice of having parallel serial numbers with the addition of a suffix, then you should combine the two in a calculation and use this as the main record ID.

Your idea for a data entry table is not a bad idea, particularly if your committed to building the scripts to make it happen. This allows you to use the power of scripting for error checking and other tasks. The main question is how do you want the workers daily work flow to go. Think about what it is exactly that they need to do, and make everything as efficient as possible. If the data entry tasks are straightforward then entering them straight into the database may not be a problem, and then you won't need to replicate standard functionality in a script. A consequence (?benefit) of your scripted approach is that the worker may not have the ability to delete or edit a record after it is committed. This could also be achieved through file permissions. One thing is for sure is that you don't want workers to be able to go in and delete the entire data set, so some restrictions on the standard filemaker menus are important.

[barkingsheltie]

Thank you mfero.

I thought about what you said today towards the end of my day, and please know your comments have given me pause for consideration.

Both of you shared enough insight, especially the last comments you made, that not only do I feel confident I can deliver a better solution now that I would have previously, I think my filemaker/database skills have been upped a bit too in the process.

Thank you both!

Shelley