So have you built anything or are you just talking about it? I did something like this four years ago, I've showed it to a few people, but I never refined it. Also it is my understanding that one of the big developers has used this kind of thing for reporting. Reporting is a good place to start if you're not willing to recreate entire structures from scratch in this model. At least it lets you attempt to solve some practical problems and see where it leads you.

Edited April 3, 200619 yr by Guest

Stll mostly talk... I mocked up a model using a singletable data file, but have already made some alterations to address readability. It is now beginning to look like a singletable logic file (giant join-all table) that attaches to a more traditional multitable datafile (similiar, I bet, to the reporting idea you mention. Reporting seems to be the place where something like this would offer the most advantage).

I am curious what your experiences with it were. 4 Years ago- I imagine FM5? What did you discover in the process?

-Raz

My discoveries? Well the idea is very fascinating, and actually there are several other things you can do when stepping down this path. My goal was slightly different or perhaps broader than yours - how far can I take the idea of driving everything in Filemaker with data? So, as you did, create table names, field names, etc. But also generalize the layouts so that you control what fields are displayed on the layout by relationships.

The same layout could use different background graphics and different field labels and different fields. Go even further - store scripts as applescripts. So the entire appearance and functionality and data model can be changed just by importing a different data set. Once a layout becomes data driven then you even have further things you can do, such as attach a user set and data set and rule set to a layout.

Maybe I'll have to send you a copy. I did update it to FM7 but I haven't done much with it. One of the problems for anybody else trying to look at is is that it was the result of a lot of experimentation and what if but hasn't been cleaned up much so it is kinda confusing.

Edited April 4, 200619 yr by Guest

OK. I can't keep quiet on this. I'm all for reusability, and I can appreciate that there may be some layout reusability available with this model.

But won't you lose some of the elegance of the layouts? Until we can create truly dynamic layouts in filemaker, you are stuck with having a static position for all fields and labels on the layout. You'll end up with data that should be shown in a certain order on the layout, being shown in a predetermined order by what reusable fields you are using.

You'll probably say that you have to plan this kind of thing very carefully before creating the database, and you're right, since filemaker 7 came out, we've all found efficiencies by doing more planning for our files. But with the continual spectre of scope-creep and new features and modules, there is just no way you can plan for every eventuality.

Once again, I'm sticking with my original view that the headache in creating a model like this for a complex database will override any benefits by a long shot.

The main benefit you've shown is ease of reporting. I'm not convinced that's even a benefit. If everything is tucked away in its own little table, it is very easy to find the data for a report.

On the few occasions where I have ever needed to have a line item report that pulls data from disparate sources, I've always been able to either find the best compromise to get the user the data they need, or I've created a utility table for that report.

The utility table method can be a bit cumbersome, but it is usually the exception. Most of these situations can be avoided by doing a thorough analysis ahead of time, and finding out what the client really wants the data for before you create the tables.

A is very smart. A has red hair.

Carrying that to one level of abstraction:

People with red hair are very smart.

No, you've got it backwards. My argument was "gravity and the laws of physics affect all objects consistently. Therefore, virtual objects (e.g. a steering wheel, or filemaker user interface) should also operate consistent with the laws of physics". I'm drawing an inferential analogy from the general to the specific. It's a "rule of thumb" aka a "heuristic". It's not a logical proof, nor did I claim it to be.

Getting back on-topic "whey don't we all use a single table design in filemaker and ignore the built-in relationship design tools" my answer is while you certainly can do that, it's debatable if you should do it, as it relies on the weaknesses of FileMaker while ignoring its strengths.

Dont get me wrong- I agree completely about the over detailed template point. I have walked down that road as well. That is what I find appealing about this model- the 're-usable' features deal with how data interacts on a fundamental level, and have nothing to do with work flow or interface issues that plague more surface level templates.

Okay. Just one more thing.

You should read this article from Joel on Software. In taking the whole idea of database schema design to this very high, generalized degree, you are what he refers to as an Architecture Astronaut. You're proposing to not see the trees for the forest.

However, I mean that in the nicest way possible. I don't mean to start name-calling.

Edited April 4, 200619 yr by Guest
Wanted to add final line to ensure I don't sound too nasty

Take a look at the lineup for Devcon. One of the topic is "Implement a user defined data model" by Jonathan Stark. Hm. Could be an application of exactly what we're talking about here. Note that if you Google for "user defined data model" you find several articles.

Find it here:

http://www.jonathanstark.com/downloads/

http://www.jonathanstark.com/downloads/Ginko.fp7.zip

Edited April 6, 200619 yr by Guest

Ginko is very nicely done. Great example.

Yes it got a very profesional touch to it, but still is the argument that Comment raised:

I believe there's a way that Filemaker was intended to work, and that the best way to utilize the application is to follow that way. It is very hard to justify this belief with rational arguments. It is even harder to come up with rational arguments against other methods: indeed, why not let scripts handle all data shuffling and all calculations?

But I have found that following what I called "the Filemaker paradigm" makes most problems easy to solve, and that those solutions are the most elegant ones.

When actual tools exists that behaves this way, with almost the same pricetag:

http://www.intersystems.com/press/2005/mac_osx.html

There BTW impressive videos to investigate here:

http://www.intersystems.com/cache/technology/demonstration/index.html

--sd

Edited April 6, 200619 yr by Guest

Almost the same price tag? The existing price tag for the single file method is zero. You're talking about completely swithching to an entirely different environment, instead of adding a new technique for FileMaker users.

I go with the one table version whenever I can. So as far as the ledge goes (see first message), I am with all those guys in New York who stood around at the bottom of the building yelling "Jump! Jump!"

This dialog is cracking me up. I can't understand almost any of it. Here's an example:

"...I am challenging all of my assumptions after realizing that relatively few people on this forum have a deep understanding of FM7+ (I fear I am one of them, and am trying to fix that)"

As I understand English, you just said you are one of the few people who has a deep understanding of FMP and that that is a problem you are trying to fix.

Okay...

All I can say is, dumbing down is harder than it sounds! Look at Søren who is so smart that no one can understand anything he writes; but then, he's Danish (I think) and Danes, as we know, are better.

Thanks. You guys made my day.

Jake

A single table?

Now that really depends on the type of data you're storing, and how you plan on accessing that data.

Think of a table as a logical grouping of data with a one to one relationship to one another. If you have any data that requires a one to many or many to many relationship, you'll find a single table schema cumbersome. The self-joins will also probably start giving you a headache eventually too. But now that I think about it, you could probably do it with a single table, but it isn't merely a cosmetic design decision. Multiple tables really do make design more logical.

(Now, I wonder if you meant, "single file structure-why not?")

Well I think the point of the single table concept being investigated here and elsewhere is its flexibility - the fact that the table and field defnition is just data. No graph, no define fields, etc. Just create or import "table" and "field" definitions.

Raz

Can you post "for me" a simple example relationship many to many (students and classes) with single table? only this

thank you advanced.

Ann

France

Sorry for bailing out of the thread, had to do some paying work for a while...

In taking the whole idea of database schema design to this very high, generalized degree, you are what he refers to as an Architecture Astronaut

That is a great term, and definitely applicable in the current thread. However, I have found that indulging in some impractical abstract theory has always changed the way I work in a markedly beneficial way (even if the result is far from the original hypothesis) once I get back to the nuts and bolts of specific projects.

One of the topic is "Implement a user defined data model" by Jonathan Stark. Hm. Could be an application of exactly what we're talking about here.

Fantastic! Thanks Bruce, this looks like exactly the same idea (just implemented better) and without the Structural tables to address readibility. Unfortunately, i dont think I will be able to make it to DevCon this year (unless someone wants to sponsor me as a heckler for this presentation...) but i am definitely going to pick apart these examples. It does seem like he was not able to overcome the create new record through a portal problem without a script (long story, but if you have been playing with this model, you probably have encountered it). Ginko appears to be much like the first "Single Table" idea I proposed, while the SingleTableJoin is almost identical to

it is now beginning to look like a singletable logic file (giant join-all table) that attaches to a more traditional multitable datafile

So, as you did, create table names, field names, etc. But also generalize the layouts so that you control what fields are displayed on the layout by relationships.

Bruce, this does seem indeed similiar, but broader in scope. The reusable layout idea seems difficult to implement in an elegant way, but an interesting excersise. I wouldn't mind taking a peek if you want to send it to me (I promise to not judge it as a representative product of yours...)

Can you post "for me" a simple example relationship many to many (students and classes) with single table? only this

Ann, Have a look at examples above. Unfortunately, while the implementation is simple, the structure that it is implemented in substantially different than the standard FM model and can cause some confusion (see this entire thread...). I would not reccomend trying to use it in an existing solution unless you are clear on everyting else (I still am not).

Anyway, I am glad to see that other people are working on this as well - I was beginning to feel a bit lonely! Skeptics and believers alike, thank you all for the spirited discussion.

Cheers,

-Raz

However, I have found that indulging in some impractical abstract theory has always changed the way I work in a markedly beneficial way (even if the result is far from the original hypothesis) once I get back to the nuts and bolts of specific projects.

Oh yes, it's the same as throwing in a new tecnique you've just learned, all over the place until you wommit - a very good point indeed, much more suiting than Bruces use of the term flexible ...when it comes to it, is it more to know when to break rules in a virtue'ish manner ...only you should have made this declaration earlier on!!!

--sd

Until we can create truly dynamic layouts in filemaker, you are stuck with having a static position for all fields and labels on the layout.

During a re-read of the thread, did I reconsider the quote above and would like to know how "truly, true" you think mergefields are? If say you're making gantt charts are they pretty usefull, because they forward or push ahead a block/massive if some empty space on the left side is required, the Graphic Formatting tools is just making this process even easier.

Agreed moving around embossing and engraving via wagon loads of different sized char-g in webdings is a little far fetched, but indeed possible, and especially am I a little worried 'bout networked rendering.

--sd

Allow me to post this question. Is not this single table idea nothing more than a really large "Multipurpose Table"? We often times use such an approach with our multi-table systems for certian data. So then, why not? The designer, however, must be kind to his following replacement who must work to modify this table. The extra documentation in this approach may very well be the largest downside.

Tim

Without reading every post in this thread, I have 1 really good resaon to have separate files (therefore separate tables). I have a solution that has over 3000 scripts, and it would be a nightmare if they were all in one file. Filemaker is primitive when it comes to maintenance: moving one scipt at a time; no way to organize scripts.

To me, the best solution is a segmenting of tables based on functionality so that the script maintence problem is minimized.

Steve

Hmm, must be a converted solution. There's no need to have that many scripts in FM7/8. I probably have a couple thousand myself, but that's because I'm running a mostly converted solution at the moment. As I go through and optimize or rewrite, those scripts get thinned out by using script parameters and scripts that serve the same purpose in multiple tables.

Anyway, your argument seems more about 'multiple files vs. single file' than putting everything in one table. With a large solution, there's still going to be a lot of scripts, and I agree with you that managing them is a good consideration for the 'multple file vs. single file' debate.

Not that I'm advocating Raz's single table approach. I've already voiced my opinion on that.

Yes, it is a converted solution, and I've gotten rid of 100's and 100's of scripts. However, even if I wrote it from scratch, and it had 1000-1500 scripts, I can't see having them all in 1 file.

Steve

Hmm, must be a converted solution. There's no need to have that many scripts in FM7/8

This is an interesting observation, it makes sense though ...since the scripts now are instances in their own right and not tied to a certain layout. But similar is it now established practice to move all layouts to TO's occuring in one file? This practice are at least reducing the scripts calling scripts in another file ...perhaps cutting the number of scripts to the half?

On top of that can a lot of standard tasks be put into merged recursive scripts since the paramter now ONLY is layout name/number and not jumps to different files. Take a look at Petrowskis video:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=622&mode=thread&order=0&thold=0

I would say because all layout's are moved to one file, doesn't allocation of scripts in different files prove anything but inconvenient?

--sd

Allow me to post this question. Is not this single table idea nothing more than a really large "Multipurpose Table"?

Exactly, it is not complicated or fancy at all in that maner. But the model conceives of it more as a single-purpose table: for the purpose of holding data. We are normalizing our workflow as developers here. Having multiple tables for data is relationally akin to encountering a client that has an in-house designed DB with "Apples', 'Oranges,' and 'Pears' as separate tables in their structure.

I almost gave up on this - found that in a certain way I was spending a lot of time building FM3 using FM 8. I put it aside for a spell, but returned to it recently because I realized I had been working on a nearly identical problem in music/perception theory for the past year or so, and was making the same normalization errors there.

I have a solid working draft together (several steps up the evolutionary ladder from the Singles Table at the Champagne Room), but I can tell that the initial legwork for a practical version will be time consuming, and the file is already becoming a bit proprietary in nature.

I have made progress on a lot of the readability issues though (and there are a lot of them - the relational recursion helps tremendously with this however), and will strip down the file to a cleaner proof-of-concept next week when I get back from vacation (it is very sticky in Brooklyn about this time). I trust I will get some good feedback on what it is lacking and how to improve on it. Plus, I hope some of you might actually get a kick out of it.

I attached a little preview for now. This is derived from a Customer-Invoice-Item-Product relational state.

Yes, it is a converted solution, and I've gotten rid of 100's and 100's of scripts. However, even if I wrote it from scratch, and it had 1000-1500 scripts, I can't see having them all in 1 file.

This is an interesting point that I had not considered. You could have a single table with multiple empty files for your scripts, but I have not really thought through those details.

It does seem like an excessive number of scripts, but regardless, it would be great to have a better way of organizing scripts in general. I think the new object functions will help a lot in normalizing scripting work as well (I think I may add a Script table).

If only there was an evaluate script step. Then you could have a field with text:

Set Field [$Field; "Whoopee!"]

Neither you nor Ender has seen my solution, so how can you decide what constitutes a sufficient number of scripts?

My point is that Filemaker offers no way at all to sort, categorize, and display large numbers of scripts. Whether there are 500, 1000 or 3000 scripts, maintenance is still a issue, a BIG issue. And dumping everything into 1 file is not my idea of easy maintenance. We can't find orphaned scripts now, so why make the problem worse (yeah, I know there's the DDR (yuk) and FM Inspector). I know you've invested a lot of time in doing this, and under some conditions it probably will work very well, but I have doubts about a large solution.

Steve

I didn't mean anything in particular about your solution Steve, I am sure if I went back and counted , some of mine would ring up there as well.

I think what we are saying is that there are many new ways to reduce the number of scripts needed. More relevantly, the issue you are describing belongs in the 'Single File Structure: Why not?' thread.

As I said above, I completely agree with you about the need for a better way of organizing scripts. Having multiple files purely as a means of script grouping seems pretty extreme to me.

Am I wrong in assuming a Single Table means a Single File? Or are you splitting a single table across 2 or more files, in which case I gotta learn to read the entire thread, not just the last page.

Steve

Yes, I think if you read the whole thread (get comfortable) it will be clear that your point is perhaps kind of related, but off topic.

What is being discussed here is the storage of user data. Could have one file, could have thousands, but only a single data table.

We can't find orphaned scripts now, so why make the problem worse (yeah, I know there's the DDR (yuk) and FM Inspector)

It would be like rubbing salt in it, to say you could poke into your documentation. But I think there is a point in making a searchable database of all your scripts and layouts - by using this tool - watch this flash animation:

http://www.schubec.com/index.php?content[v][title]=fmpasteboard

This is the the URL to the Mac tool doing the same:

http://www.quart-edv.de/plugins/pasteboard_en.html

--sd

Edited July 14, 200619 yr by Guest

I think that turning a DBMS with some relational capabilities into a manually controlled DBMS based on the hierarchical model, which was proven decades ago to be sub-optimal is not a new or even in my opinion a good idea.