single table structure- why not?

[Ender]

Your solutions to these seem to involve adding more complexity to this already complex system, but you gloss over them as if they are self-evident.

For example #4, where you say filtered relationships are "No problem at all." Well, how? Your relationships between the data consist of a parent to child via a single key field. So if you're filtering, wouldn't you need to add additional relationships? Same problem with range relationships and multi-criteria relationships.

[comment]

There are many ways to make Filemaker work. For example, we often see here a "dumb" file that is worked almost entirely thru extensive scripting.

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.

It is a different way of working, but it is much easier to see reusing soemthing like this than more traditional structures.

Let me take one more stab at this 're-usability' issue. You can make a refrigerator suitable for tropical climates. Or, you can make a very cost-effective refrigerator. Try to do both at the same time, leaving the decision to the last minute, and you will end up with a unit that is twice as expensive to make and breaks twice as often.

If I am making an invoicing solution, my primary goal is to make the best invoicing solution for the particular situation. I am not at all bothered whether I will be able to re-use this solution for tracking ads in a newspaper. I firmly believe that if such consideration entered my mind, I would end up with less than optimal invoicing solution.

Granted, I might have a basic invoicing template. I might even have a more basic join-table template. But the most basic template is a new file. Anything already defined in the file could be contributing to my primary goal - or hindering it.

[Howard LeCanard]

Raz,

This whole exercise seems to be a lot of work to go through to make sure that you do things differently. I can understand that imports, and data transfers may seem easier if you're new to FM7/8, but once you get used to doing things the new way, I think you'll find things aren't as tough as they were.

In FM6, if you wanted to script an import of a bunch of data, you had to go into each affected table, and create scripts to prepare the table, perform the import, then do any further processing. Then you had to create one script that managed all these import scripts.

In FM7, you can do it all in one script. Just change layouts to work on a different table.

You can develop a solution your suggested way, and have to document what each field is based on what type of record you are storing ... OR ... you can develop using the tools FMI provided, the way they intended, and not have to look at your documentation to discover the use of every field.

I prefer to look at a field name and know what it is. For example, a field called NameFirst, in the Contact table is the first name of a contact. What is field Text1 when the record is a contact record? Better look at the book that you wrote to document the solution!

All that being said, it would probably be a fun exercise to create a solution the way you suggest. I just wouldn't want to have to maintain it!

[Razumovsky]

Apologies,

The design is still new to me and was not 'simple; at all until it finally clicked in (in the course of an hour the number of table occurrences dropped from 16 to 3). If I am glossing over some things, it is because I haven’t had to explain them to anyone yet. The key difference is that this model evaluates everything from the datas perspective, where a more standard design requires the data flatten out to our perspective before we interact.

In The Data TO, all needed relationships between all data exist. To access remotely related data, a record stands in the Data TO,a sks a child record what it would see if it was in the middle, who asks its child records what they would see in the middle, who asks its child records what they see... I don’t mean to say that all can be accomplished by two relationships, just that there is no need to duplicate relational states. You only need to create one 'heart' that essentially the representation of all possible states of relation within the DB.

Yes, your question about filtering would require an additional relationship (just as it would in a multi table approach), because it is introducing a new state of relation, that of a parent being directly related to a child and its grandchildren at the same time. However, this relationship would only have to be made once, and from the Data TO. Now all data can exist in this state of parent-child, Child-parent, Parent-grandchild relation. Without changing a thing but the record you are viewing, a potrtal that would display all products ordered by a certain customer on a customer record would display all Items on all purchase orders related to an invoice from an invoice record.

It is not simple, but is it really more complex than a standard graph? I would maintain that it is actually much simpler, as the relational state of the data is crisply defined and easy to access. No more searching all over the graph to see where else something is related, it only needs to be considered from a single point.

Comment,

I get your points about the Filemaker Paradigm and re-usability. I think that this sort of relational structure is actually more in line with this paradigm. It does not try and do anything fancy with the program, but rather eliminates redundancies and allows FM to only have to consider things directly related to each piece of data. There is no distantly related data, it all occupies the same centerpoint.

"If I am making an invoicing solution, my primary goal is to make the best invoicing solution for the particular situation."

I think that there is much wisdom in that approach. Now, let me ask you what your approach would be if you were designing a product for a database developer to streamline their process of database creation. You are hired as a Meta-Consultant, if you will. You would point out that many of his projects have a remarkably similiar relational structure. You would notice that within a single project, identical relational structures are being created and employed all over the place. You would observe that much time is spent adjusting and tweaking this structure over the course of the project. You would notice that this tweaking does not come easily, and is inhibited by tables. These are major inefficiencies in the work process, and your solution would aim at eliminating them as much as possible without creating more work in the process. Yes, it would be overkill for a one-off invoicing system, but it would be perfect for me.

Readability issues aside (I don't think those are insurmountable), I don’t see much against this method than a belief structure, all be it a largely held belief structure. The data is actually happier in a single table state.

[Howard LeCanard]

You would point out that many of his projects have a remarkably similiar relational structure. You would notice that within a single project, identical relational structures are being created and employed all over the place. You would observe that much time is spent adjusting and tweaking this structure over the course of the project. You would notice that this tweaking does not come easily, and is inhibited by tables. These are major inefficiencies in the work process, and your solution would aim at eliminating them as much as possible without creating more work in the process.

Raz,

I think you are really over-estimating the time and effort involved in making schema changes. Adding a table and relating it to your current structure, or changing a table's place in the relational structure can be done quite quickly, even in the most complex of databases.

As a meta-consultant, I would recommend that the client build a good template file, that has a reliable contact module in it. After that, I would tell him to make sure that he has a firm understanding of data theory, so that it is not difficult for him to create new tables, TOs and relationships on the fly. After all, I've seen a lot of extensive template files that really restrict the developer (okay, ME) by having too much created ahead of time.

Since all our clients have different businesses, expectations, and desires, there is no way to streamline our processes too much. A lot of it is a creative process, just like for a Web developer. The Web developer may have a few template CSS pages that he starts with, but there are too many variables in each project for him to go beyond that.

[comment]

what your approach would be if you were designing a product for a database developer

Someone already did that - it's called Filemaker. It has enough limitations already, thank you very much. Ask me instead whether I would use Filemaker if it came out of the box using your paradigm.

[Razumovsky]

Thanks again for the feedback- it remains very helpful.

Adding a table and relating it to your current structure, or changing a table's place in the relational structure can be done quite quickly, even in the most complex of databases."

Yes, very easy. However, re-normalizing your data is not. Neither is creating reports with data from multiple distant related tables. Imagine a calculated multikey that could access all data in the system. Set that multikey correctly, and you can display any data you want. All this would take in a singletable model is creating a single relationship from the Data TO: CalcField=MatchID. Now all data could access all other data directly through a single portal.

Also, you cannot script in FM's define fields, and many features of importing still require manual attention. All of these could be controlled through scripting in what I propose.

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.

I am resigned to the fact that I won't be making my fortune selling SingleTable Developer kits to the masses, but I cannot find any limitations that this model brings (beyond the mentioned readability ones, which appear resolvable), and see many multi table limitations that it overcomes. If there are limitations imposed by this model(beyond the cosmetic), I would like to know.

Ask me instead whether I would use Filemaker if it came out of the box using your paradigm.

It does come out of the box using this paradigm, we just choose not to use it in this way, and for good reason. It demands a more complex understanding of things. I think the real question is if a more complex understanding would help the process in any significant way. That remains to be seen, but at the very least it has given me a much more accurate and readable model of how complex systems relate.

-Raz

[Ender]

...If there are limitations imposed by this model(beyond the cosmetic), I would like to know.

Well, you can lead a horse to water...

[Howard LeCanard]

I am resigned to the fact that I won't be making my fortune selling SingleTable Developer kits to the masses, but I cannot find any limitations that this model brings (beyond the mentioned readability ones, which appear resolvable), and see many multi table limitations that it overcomes. If there are limitations imposed by this model(beyond the cosmetic), I would like to know.

I don't think I'll be able to change your mind, Raz. You definitely haven't changed mine.

And now, I am bowing out.

[comment]

It does come out of the box using this paradigm, we just choose not to use it in this way, and for good reason. It demands a more complex understanding of things.

I am not sure I agree to the first part, but that's not important. I agree there's a very good reason not to use in this way. But I would state the reason as "It demands we think in computer language instead of in human concepts". You might think this is a minor point - I consider it crucial.

There's absolutely no logical reason why turning a wheel clockwise should make a vehicle turn right, instead of left. Still, no car manufacturer would ever consider switching. One important role of software is to provide a usable interface to the humans involved in the process.

[xochi]

There's absolutely no logical reason why turning a wheel clockwise should make a vehicle turn right, instead of left.

Actually, there is a very good reason for this. Imagine if the steering wheel were, in fact, a wheel sitting on the ground. If you rotated it clockwise, which way would it go? To the right. It would only go to the left if gravity were reversed and objects stuck to the ceiling instead of the ground.

There are similar exaplanations why "up" generally means "more" and "down" usually means "less" with manually operated controls. Humans have built-in physical analogies for abstract concepts.

Edited March 30, 2006 by Guest

[comment]

There IS a very good reason for it - but it is not a logical one. In fact, you have given us a prime example of associative thinking. All in all, you are only strengthening my point.

[xochi]

There IS a very good reason for it - but it is not a logical one. In fact, you have given us a prime example of associative thinking. All in all, you are only strengthening my point.

Objects behaving according to the laws of physics is, in my book, a pretty darn good (and logical) explanation. Carrying that to one level of abstraction is still logical, though one can argue it's not causally necessary.

[Razumovsky]

I think I am not presenting myself clearly. To me, the readability issue is the only valid point brought forth so far. I do not mean to discount that point, but would like to investigate if there are any structural/technical flaws with the model. Closer to the beginning of the thread, people said:

If solutions like yours were always feasible, we would still have our data in spreadsheets such as Excel.

...I'm sure it can be done, but it doesn't sound easy...

...And it sucks at separating data from design

...I think the disadvantages are quite clear. The advantage is not.

3. What about stored calcs? Is this not possible, or did this not seem like something that would be useful?

4. It doesn't look like filtered relationships are possible in this solution. How about: Multi-keys? Range relationships? Multi-criteria relationships?

5. You seem to say that looking further up or down the chain of relationships than the immediate Parent or Child record is unnecessary (or at least, you don't account for it,) but I've found the ability to view grandparent and grandchild records to be a great benefit of FM7/8. For example, if you wanted to see all the Items that a particular Customer has ordered in the past.

These objections no longer seem to be present. I think I have demonstrated that not only is it possible, but a single table model is pretty easy to implement structurally, and easy to adapt to new needs. I think I have also indicated some of the advantages that come with this model.

I agree there's a very good reason not to use in this way. But I would state the reason as "It demands we think in computer language instead of in human concepts". You might think this is a minor point - I consider it crucial.

Yes that is the only issue as far as I can see. I realize that I have been glossing over this in a fashion, but I do not consider it a minor point at all either. It in fact could ultimately be the deal breaker. No matter how comfortable I get with the model, no matter how readable it seems to me, if no one else could/would interact with it, it would be worse than not giving the client the master password. It would be forever wedding me and the database in a certain sense. Way too much commitment for me, and not terribly fair to the client.

I don't think I'll be able to change your mind, Raz. You definitely haven't changed mine.

In spite of my rhetorical excess, I have no intention of converting the world to a single table approach. It has proven a valuable exercise for me, I realize that others may find it a waste of time. You are welcome to play with the idea or disregard it as you wish.

I think I will pursue it more though. If asked to justify it practically, I will tell myself that I am building a normalizing tool. Something that I can do a scripted dump of all 36 files from this legacy database (all of them with InvoiceItem1, InvoiceItem2, InvoiceItem3 fields...) into, experiment with some different ERD models, render new field, table, and relationship lists, and then dump them back into new tables for a more traditional and accessible final product. I believe it could be a useful tool and will let me confront some more of the readability issues along the way.

I will try and post a copy in the Champagne Room when I get one up and rolling. Spring just arrived here, so it may take a while to get on it...

Thanks,

-Raz

[comment]

Carrying that to one level of abstraction is still logical

A is very smart. A has red hair.

Carrying that to one level of abstraction:

People with red hair are very smart.

[bruceR]

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, 2006 by Guest

[Razumovsky]

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

[bruceR]

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, 2006 by Guest

[Howard LeCanard]

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.

[xochi]

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.

[Howard LeCanard]

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, 2006 by Guest
Wanted to add final line to ensure I don't sound too nasty

[bruceR]

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, 2006 by Guest

[bruceR]

Ginko is very nicely done. Great example.

[Søren Dyhr]

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, 2006 by Guest

[bruceR]

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.

[Jake Sterling]

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

[coconutt2000]

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?")

[bruceR]

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.

[librone]

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

[Razumovsky]

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

[Søren Dyhr]

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

[Søren Dyhr]

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

[Tim W]

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

[SteveB]

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

[Ender]

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.