[PLEASE HELP] Advice on field definition

[Eduo]

I need some advice on the best way to lay out the fields and tables for a unusual database I'm designing.

I've decided to make a database for my cousins and a game they play called "Heroclix". I looked around and couldn't see any ready-made FM file for it, so I decided to use that to learn a few things about FM.

This database would contain the figure's stats and would have at least one table for the figure data itself, plus another for teams and another for power definitions. All these are values taken from the figure itself and not necessarily calculated.

You can see here a sample figure entry from the HCRealms figure database.

As you can see a single figure has a lot of fixed information and among this are the "clicks", of which each figure can have up to 11. Each click has four numeric values (one for each combat stat) and possibly a color value for each combat stat (the powers).

I have all this data already in an excel spreadsheet, separated in several tables. One for the figure's stats and then one for each click. In each click's table I have the Figure's ID to link it to the main listing and 8 fields (one for each of the four numbers and one for each of the four possible powers).

Now, my actual question is: What would be more efficient when thinking in the FileMaker way (which is SO different from MySQL and I have been doing so much MySQL lately that I can't think straight in FM ways). I have thought of three possible ways

1.-To have a single figure table (the data does not change ever) that would have at least 100 fields (88 are just for four powers plus four stats times 11)

2.-To have a figure table and a click table. The figure table would hold the figure's main stats and the click table would hold 90 fields (4stats+4powers times 11(one set per click), plus 1 field for figureid)

3.-Same as above but the click table would hold 10 fields (4stats+4powers+ClickID+FigureID) and the clickID would say if the data corresponds to the first, second or any other click position.

4.-One figure table and 11 click stats tables, with each of the latter holding 8 fields plus a HeroID field.

I have aready tried the fourth above and, although I made it work, the relationships graph is overkill (11 instances of each of the 4 powers databases, for example) and I'm sure there is a more elegant solution.

Could you either suggest a solution based on my attempt at explanation above or by seeing the URL and considering that, in total, there are 1028 figures in the database?

Thanks a lot for any help or pointers.

Eduo

[Eduo]

I should also add here, really quickly, that the reason I'm confused in this, which may simple to you all, is that in MySQL I would try option three above and do a query in the reports which said, paraphrased: "from Click01 to Click11 show me the stats for speed, attack, defense and damage" as the results table for the figure and it would look pretty much like the webpage I linked to. But in Filemaker's reports you have to have thought your results already before this, as well as having made your calculations and all.

I used to heavily use FileMaker ten years ago or so (I know, lots have changed) and although I HAD to move to SQL for the job requirements I've always held it in a special place and I got FM7 as soon as I could, thinking I could grasp it again quickly (like the saying about learning to ride a bicicle) but have discovered that SQL has severely tainted my intuition in a database program that, for the design part, relies a lot in intuition (as it's easy to miscalculate a database's design and having to start from 0 because you didn't foretell a specific need).

So thanks for your patience. Really.

[bruceR]

You would do it the same way you would do it in SQL: One main table, one click table, click table has fields FigureID, ClickRecID, ClickNumber, ClickType, Color.

[Eduo]

Bruce:

I posted the question as well in the relationships forum, after a suggestion to do so. I also posted a sample attachment with one of the ways I had been able to make it but I think it may not be the best way.

This is the link to the thread: [url="http://tinyurl.com/yv8ay"]http://tinyurl.com/yv8ay

It's different to SQL in one way, and that is that I can't put the data in the table and manipulate it in the output report. I have to do the calculations beforehand if I'm manipulating data and this little different is making me harder to picture the database as a whole beforehand (which I know is the correct way, by the way, but I had thought this project would've been simpler, what with the fact that no data has to be entered..:

Eduo

[bruceR]

Sorry but I can't follow any of that.

OK, I looked at your file, it's a mess. You would do the same thing in SQL as you would in FMP. But what you really need to do is get a better grasp on data normalization. You need about two tables for everything you're doing.

[Eduo]

Bruce: I know it's a mess. One of the reasons it's a mess is because the original data files were so as well and I tried working with them without modification.

I've remade the thing and have now only two tables, actually, although several instances of these in the relationships table.

I did say I needed help, tho'..: That's why I especifically asked for what to do and what had I done wrong.

Anyway, I'll keep going at it and figuring things out. Considering there is nowhere in Spain where I can get a course or even a book I'll do my best until I can get one from overseas.

Thanks anyway

Eduo

[Eduo]

[DELETED] Broken Post PHP

[Eduo]

I've been browsing around and it seems repeating fields are NOT the norm, but the exception. Why are repeating fields better in this case other than to simplify relationships?

Also, how can I import into them? Is there any sensible way?

Eduo

[bruceR]

That's why I asked the question about reporting. The problem with repeats is that you can't individually sort or select or delete them. So with the repeat approach, you can't for instance show all column 3 records for hero X where color = blue. That's why I originally suggested the approach you would take with other systems like SQL systems.

Repeats are a holdover from the days when FileMaker was flat file and repeats were the only way to get line items, for instance invoice/invoice items. Importing can be done but is a little tricky.

[Eduo]

I thought as much. Information doesn't change per se, but I might need to pull information from within the data itself.

I've been thinking about this problem, and in the end I've realized that clicks or not, colors or not, in the end it should be a flat-file database for the heroes themselves. All those 11 click numbers (times two) and their corresponding colors are not different from a flat-file database of products with their characteristics (say, like a people database that lists their birth date, their sex, their hair color, etc.)

So I'm probably going to use a flat file for the clix (although that does mean almost 100 fields for it). My only doubt is now how to use a single powers table for reference of 44 power colors (I have 44 fields in a single record for powers, 11 for each category, and each of these has a number, which has a reference to a color power, as you already saw). I can't think of any way to link these powers to the colors database, but I don't think I can do it without 11 iterations to the colors database.

I also have to admit, I think I might have mentioned this before, I'm at loss with the use of global fields. I can't get how they work. I haven't seen an explanation of them anywhere that gave me a clear understanding of how they work..:

Thanks for the help and the tips, too. O think I'm at an even more basic level in FileMaker than I expected (my previous attempts, although successful, were admittedly easier than this!).

Eduo

[bruceR]

You REALLY don't want to do that and don't need to do that. It's a big mess and it really is time to learn fundamental database ideas. This has very little to do with Filemaker and everything to do with basic data normalization. What exactly do you mean by "pull information from within the data." ?

It will certainly be much harder to do with your "flat file" which will be even worse to for creating reports. At least with the existing repeat structure, you have individual records which you can search, sort, omit, etc. You can't to any of that with your gazillion field system.

If you take the example I showed you, eliminate the repeats, and add a ColumNumber field in FMClix. you're done except for modifiying the relationships to get it to display correctly.

[Eduo]

Sorry for the delay in answering. Left town for a day.

(I should add that I've taken your advice and have been actively looking for some tutorials and training on data normalization and database design for relational databases)

Ok. So it'd be better to leave the dissimilar information in a flat table while the clix themselves in a separate table and reference them by column, meaning that I'd ask, for a specific figure, the value for Click1 where the type is (for example) Speed, right?

I did think about this and started doing it and it's actually simple for me to do and removes a LOT of redundant blank fields and data, but here's where my knowledge of FileMaker is limited, as I'm not sure how to display the figure's clix in a table-like manner (the same way it was shown before, actually).

In PHP I would do it with a query, running a loop from 1 to [highestColumnNumber for the figure] and then showing each of the field's, padding the unfilled part of the table with blanks (for aesthetic reasons in the table).

This above is what I can't figure out in FileMaker. I know I can't make this selections from the layout itself, but from a previous field/calculation table but I'm not sure how this is done.

Eduo

[bruceR]

You do seem to be on the right track. Filemaker adds a new wrinkle that doesn't exist on other databases as far as I know - the ability to display portals with starting and ending row numbers. Take a look at the Business Tracker example from the Filemaker site and the product catalog layout, it is similar to what you're trying to do. Your need to create a grid style system does complicate things.

[Eduo]

It's not exactly a "need". It's more a "nice to have" for my cousin. It'd be easier to understand and, not only that, would make the grid much shorter (4 rows high instead of 11 rows high).

Incidentally, after a lot of data massaging I managed to get the data files correct and could conceivably, right now, create the grid as required with a vertical layout in a portal (4 portals, actually, one for each category). Horizontal portals would make my life easier..:

I have thought getting the portals by ColumnID (I have a field with columnID, 1 to 11), but that'd mean making the 11 relationships again (which admittedly is better than 44, but is still a little overkill) And I would have problems of other kinds (like sorting within each portal).

While I think about the layout I'll try to work the vertical layout into the database. Thinking about it it shouldn't be a bad thing anyway, just a different way to see the data.

A bad side effect of the "new order" of the database is that the FP7 file now is much bigger, weighing in at 3 megabytes without any graphics in it. And this is happening even tho' I made sure to not include "zeroed" fields (if a figure had only stats for three clix I didn't add the rest of the clix).

Any way, thank a LOT for all the help. This has been an enlightening thread and I know it must've been frustrating for you (I can only imagine your face when you first opened my original file..:

I'll try to work on the layout now, I have thought it should work as well and probably look a little more compact (an original goal) with a smaller vertical grid instead of a horizontal grid (literally rotating my original grid 90 degrees clockwise).

When I'm done with this I'll try and find out how can I use an already-filled field to become the reference for external image files into containers (the images for the figures themselves, I've got the path already in a field) and how to convert a currently-text field into a clickable URL (the URL already exists in the field).

Thanks a lot again. If you keep subscribed to the thread I'll post a sample of the file afterwards in case you want to take a look at it.

Greets

Eduo

[bruceR]

But as I mentioned, horizontal portals are possible, that's why I pointed you to the example file. Did you look at it and what did you learn? You do not have to change any relationships. it is a matter of how you DISPLAY the portals. What you do is create single row portals, first one 1 row, next one is row 2, etc.

And you still have not answered the question about reporting. What exactly are you trying to do?

[Eduo]

I did take a look at all three examples for horizontal portals I've found, including the business tracker and the ones in the forums, but I'm afraid I can't understand how they're made.

I'm trying to see if there is any place that explains the portal (rather than just giving an attachment, or along with it, at least) to understand what's being done.

I hadn't noticed I hadn't answered on the reports.

Once I've got the database set-up for the basic display of data I'm going to try and set-up both a way for a user to record which figures (and what quantity of each) they have, as well as some rudimentary ways of forming teams (all figures have a point value, so setting up a limit could send the database into trying to find several figures in that team according to some rules). Other than that there's no other kind of reporting in mind.

I had planned on setting up a listing of all figures in the same layout as the figure's stats are now being shown, but haven't gotten around to even finding out if that's possible (it's not exactly a portal, as the figure listing is actually the parent table of the detail listing and that may not be possible to do currently).

I've already solved the other doubts I had about the images and the links too, they were much simpler than I expected, but then again the features weren't there when I last used FileMaker..:

Eduo

[Fenton]

OK, here's the text of one of my previous posts.

The way that you get all the values of a related field (a portal is just a way of viewing the fields of the records matching a relationship) is to use the Design function "ValueListItems(Database name, Value List name)." There are a few steps.

You must first create a value list which has the values of the relationship. You do that by "filtering" the value list, choosing "Values from a field," then "Only related values." You specify the relationship of the portal, then the field which has the items in question. Let's name this value list "Items."

Next step is to create a calculation field to hold the above, using ValueListItems. It must have its Storage Options set explicitly to "Do not store calculation results".

_cItems_VL = Substitute ( ValueListItems ( Status(CurrentFileName), "Items"), "

[Eduo]

Cool. This can get me started and better understand the examples. Thanks.

I'm off to bed for today, it's 5am here!

Eduo

[bruceR]

None of the reports you describe conflict with the repeating field approach i posted because you do not appear interested in breaking down the click values. If you need to break points out in detail - show which figures have a click value of 5 in speed column 4, or average click value in column 10 for the green team - then you would need to break it down into individual records.

Regarding the portals, I thought we already went over that. Each portal can have a starting row number and a row count. So to display a "horizontal portal" you make a one-row portal that starts at row 1; a second one row portal that starts at row 2; a third portal that starts at row 3. But this means you have to have values - possibly including empty values - for each row and column.