Table design help....!

[dimension]

Hi, I need some advice on table design. Specifically, I have an table which contains many sub-classes.

Do I use ONE table with many, many fields OR use MANY tables containing ONE class of asset?

Thanks in advance! :)

Edited July 3, 2006 by Guest

[Ender]

It's hard to say for sure as you haven't provided a complete picture of how your assets differ.

In general, it's better if similar data is together in the same table. If there are large groups of fields that only apply to certain types of assets, it may be useful to put those into a separate table related via the primary ID (a rare case for a one-to-one relationship). If you have a number of similar fields (Install Date1, Install Date2, Install Date 3, etc.) it's best to break those out into a separate table via a one-to-many relationship.

[dimension]

...There are many un-related fields for the asset class. assets, for example would have fields unrelated to the or assets etc. (isbn, mediaType, contentList)

Should I just use a master table and go the way of;

media_isbn

media_mediaType

media_contentList

stage_size

stage_weight

stage_suitability

stage_singleUser

ect, ect, ect...

Thanks!

[Razumovsky]

Hmmm, I think more info is still required here. As Ender pointed to, what fields would they share in common (what makes them all 'assets')?

[dimension]

...They are all as opposed to being , , or .

'Asset' as in property. There are global fields in the table, of course...

key

id

description

second-user YN

cost

purchase date

condition

quantity

incomplete YN

ect, ect.

These fields (above) apply globally to assets , , , and .

So there are many other fields that are simply not applicable to certain tables (or assets)

Thanks!

Edited July 3, 2006 by Guest

[Razumovsky]

In FM speak, 'global' means a field that contains a value that is the same for all records. I understand what you mean though.

You will likely want to have all of your Asset-universal fields in one table so that you can do things like easily obtain a total cost of all incomplete assets from a certain date range and the like.

The second part is a bit more flexible and will depend on your workflow and preference I believe.

I would choose to keep them all in the same table like you presented in your second post, so that auto-enters, lookups, and stored calcs can fire without scripting. You will sacrifice some readability and introduce some redundant fields, but for me, the trade off would be worth it.

The tricky part will be in creating the interface through which to add the data. It would be awkward to have to put 12 fields in a portal where only 3 would need to be filled out for any particular asset type. I think I might go for Proxy Fields to get around this.

[Genx]

I would be very cautious about splitting your assets table. I did so only because there were only around 90 common fields and 370 uncommon fields between 4 groups - simply for ease of field location when designing.

I still sort of regrett doing it because of the ammount of hassle it caused me regarding having different layouts for different asset types if you would like to call them that - but never the less, in the end, the data split itself turned out quite nicely. Once again though - had i not split the data in the first place, i would have saved myself a lot of time by not having to utilize a very long list of work arounds.

So in answer to your question, if the amount of fields is small, and if your usage of the data is basic, i would suggest placing all into one table.

~Genx

[Ender]

ect, ect.

(etc.,etc.?)

It's that etcetera that keeps holding us up. From what I've seen so far, this could be done in one table. But that depends on what those asset-type specific fields are.

My guess would be that there's not that many asset-type specific things to remember about Books, DVDs, Intellectual, and PDFs (I don't know what "Physical" is in your context).

As for making an interface to handle this, I don't see that as a problem. I would guess that only some of those universal-asset-type fields are really needed on a combined list layout. The detail information can be entered and viewed on asset-type specific layouts.

[Razumovsky]

I guess I am just looking for a good place to use my new toy Proxy fields...

They would allow you to design only one layout to handle all asset types, but would also be more confusing (and prone to error) for a newbie.

[dimension]

Thanks for the replies!

I think it would take more work and effort splitting the tables, especially in the scripting dept. which I'm just getting used to (again) - I used 4D for many years on the Mac, and I really miss its Pascal-type coding.

I have also just purchased all the DACONS extensions, and at the minute am really struggling with FileMaker's scripting techniques. This is in between learning XBase on FoxPro 9(!)

Just an unrelated (no pun intended) question; why after all these years has FileMaker still not implemented 'real' buttons and text-based scripting; just a 'highlight' effect on a button doesn't really cut it and not being able to edit scripts in detail can be a real pain...

Thanks!

In response to ; 'Physical' assets in my database differ to 'intelectual' assets because one is real, and the other is just electronic ( an Acrobat document).

Edited July 3, 2006 by Guest

[Genx]

Because if they had they would have had nothing to upgrade in FM 9 :