Jump to content

[PLEASE HELP] Advice on Relationships definition


This topic is 7553 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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

I posted this to the fields forum, but was suggested to post it here and would get better results.

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

Posted

I suppose any of those structures would work. I'd probably opt for the 3rd design, unless there was a compelling reason not to. It's easier to reproduce the look of that click table with regular portals in this design.

Sometimes the ideal structure is determined by how the data will get entered. Sometimes it's determined by the reporting requirements. In your case, you probably don't need to do any reporting. So if the data entry works in option 3, then I think this would be best.

Posted

Ender:

Actually the data is fixed. Is a database generated by the actual stats in the figures and they never change. The data is there for maybe later filling out owned figures, creating teams, etc.

As all of these would either just be pulling information from the fixed figure database or entering data in a new table (a "teams" table, or an "owned" reference, or even "loaned) I just wanted to know what the best way would be to store the data (most efficient, if you will) both in terms of performance and in terms of pulling the data afterwards for review (like in the sample above.

I already have a quick and dirty design where I'm using the 4th option, but I'm considering alternatives as I'm not finding ways to manipulate the data afterwards.

I'm attaching my Heroclix database as it currently is and maybe you could take a look at it to understand what I mean about the spidery relationships. It's become unmanageable.

I also wanted to know the best way to set this up so as to be able to afterwards pull information more easily.

Also, if you see the database and wonder why is the data so weirdly referenced it has a lot to do with the original Excel worksheet I had to take it from. I had never seen so weird Excel macros in my life and the way those macros were pulling the information from blocks of text (not tables) I can't even start to explain it.

THanks for the help. I haven't touched Filemaker in enough time that the experience is humbling me..:

Also, keep in mind that the attached file is a subset of data, only 100 records or so. The original tables I'm using have 1028 records for each of the tables (that is, for the hero table and for each of the "clix" tables).

eduo

FMClix small.fp7.zip

Posted

Since you're not using portals for the Clix, could you use just one Clix table, relating to it by HeroID AND Row AND Column?

Row 1 would be Speed, Row 2 would be attack, etc. The relations between Clix and Colors would then be by ClixPower AND Row (you could still have different color sets for Speed, Attack, Defense, and Damage.) You would still have 44 relationships to Clix, but only one relationship to ClixPower.

Posted

Or better--Use 11 portals (with 4 rows) related by HeroID AND Column. The rest would work as described above.

Posted

This is an interesting idea and I hadn't thought about it. I think you stare so long at a problem you can't help not noticing any way to do it differently. Thanks for helping me to see the problem, almost literally, sideways..:

I'll do this to see how it goes, it's not like this is for a work project or anything..: I'll try with a smaller clix database (3 clix or so) and see how it works.

I'm not using portals right now, but I plan on making a layout where the list of all the heroes are visible (a portal, most likely) and upon clicking any of them you see, in the same screen, the stats of the selected item. If this works then I might expand this layout to include a "team" portal that shows "selected" heroes to form a team. This would mean two portals and a detail view all in the same layout so it may prove to be a challenge..:)

I accepted to do this for my cousing also because that will be a way to get myself acquainted with FileMaker again, since not using it intensively for six or more years..:)

Thanks again. Any further tips would be greatly appreciated.

Eduo

Posted

BTW. After my last message I realized that if the data is not going to be changed by the user (is just a reference table) *AND* it's always going to be laid out in the same way for everybody THEN there was no reason for it not to be a single MAMMOTH table.

1 table for heroes, containing the stats and the 11 clix with values and another 11 clix with powers.

In total instead of 12 tables (one main stats, 1 for each clix) and a mess of relationships I have a single table and no relationships.

*sigh*, again, thanks for forcing me to put this in another light. The database just got much simpler.

Also, while in this. I have tried putting the images to the figures by references. The references are really simple: "images/"&HeroID"*.gif"

I have tried every way I can think of and I can't make the image display. I've defined the field as a container with auto-entered data specifying the path and I have also tried creating a calculation field and the result being a container.

In all cases I get a container box with the TEXT to the file in it. I'm missing something basic and can't figure out why. And whatever I'm missing is being taken for granted in all the forum topics I've tried searching for here..:

Eduo

Posted

What kind of reporting are you thinking of doing? And are the colors and numeric values just entered manually, there is no lookup or logic to them? It may be you could get by with repeating fields.

FMClix small.zip

Posted

Actually I want to do two things. One of them is like the one I linked in my first message and which, messy as it is, shows my sample database: A figure's full stats.

The second thing I want to do is be able to set-up two other groups, one of them the figures owned by a person, and the second would be teams based on those owned figures.

I think I can do the other layouts but I wanted to find the most efficient way to build the main database. I can manipulate the data in any way to fit the best way and then import it into the database, but I *know* the design of the database will affect greatly what I do with it in the future and also the "cleanliness" of the overall file. That is what I was asking for. I'm severely stumped and can't think of any way do it better. I have already tried the one I sent (tens of tables) and I'm also trying right now with a second method (which has just one table for all the figure's stats) which isn't proving any better.

All the data is fixed and will be locked. It's just for team building and statistics.

Eduo

Posted

Wow. I hadn't noticed there was an attachment, so involved was I in trying to explain..:

Thanks a lot! This is what I needed!

I have to admit, and I think it's clear: I have ABSOLUTELY no idea exactly how the global field works. Everywhere I've seen seems kind of confusing to me.

This is a great starting point. I'll take it from there. Thanks again and thanks for your patience!

Eduo

This topic is 7553 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.