Transclude and Reveal question for a novice

[Toddler Wiki]

Hello,

I am fairly new to TiddlyWiki and I am trying to use the transclude feature to build a data dictionary for my data warehouse.

Generally... What I have a tiddler for every field and every table that in the data warehouse. The field tiddlers have tags that match the table tiddlers.

A general field tiddler looks like this…

Tiddler Name: Field_Name_01

Business Description: Random Text about the field

Data Type: varchar(25)

Inside of the tiddler there is a list for every table that the field is in

• Table_Name_0

• Allow Nulls: yes/no

• Original Name:

• Column Notes:

• Table_Name_02

• Allow Nulls: yes/no

• Original Name

• Column Notes:

• Table_Name_03

• Allow Nulls: yes/no

• Original Name

• Column Notes:

The tiddler uses a simple list where the code looks like this.''Business Definition:''

 

''Business Description:'' Random Text about the field

 

''Data Type:'' varchar(25)

 

* [[Table_Name_01]]

 

** ''Allow Nulls:'' yes/no

 

** ''Original Name:''

 

** ''Column Notes:''

 

* [[Table_Name_02]]

 

** ''Allow Nulls:'' yes/no

 

** ''Original Name:''

 

** ''Column Notes:''

 

* [[Table_Name_03]]

 

** ''Allow Nulls:'' yes/no

 

** ''Original Name:''

 

** ''Column Notes:''

 

 The table tiddlers use the following code

<div class="tc-link-info">
<$list filter=" [tag[Teller_Transaction]] ">
<div class="tc-link-info-item">
!!! <$link><$view field="title"/></$link>
<<<
<$transclude/>
<<<

I would like to alter the table tiddler so that it only shows the appropriate information for that table. Preferably I would like the information to be expandable but default to whatever table is being looked at and if only the field tiddler is being looked at then the all the information would be expanded.

As an example for a table tiddler…

Tiddler Name: Field_Name_01

Business Description: Random Text about the field

Data Type: varchar(25)

• Table_Name_01

• Table_Name_02

• Allow Nulls: yes/no

• Original Name:

• Table_Name_03

• Table_Name_04

I know that I will probably have to add something to the field tiddlers as well.

I hope someone can point me in the right direction or just provide some instructions on how to achieve this. As I said, I am very new to tiddlywiki so all simplified responses and patience would be greatly appreciated.

[Tobias Beer]

Hi there, Toddler,

May I recommend you change your naming conventions?

I would recommend you title your tables as they are, e.g.:

SomeTable

and then you title each field as:

SomeTable.SomeField

Why? Because...

1. there may be different tables with the same field
2. searching for the table will also give you each field in the results

Here's a little demo that shows you how to display fields for a table based on the above:

http://schema.tiddlyspot.com

As for your meta-data or otherwise related data, here's what I would recommend:

1. put simple, oneliners like yes/no into a field of each Table or Field tiddler
2. put exhaustive related information into their own tiddlers, e.g. SomeTable Notes

The above allows you to further aggregate lists for all Tables with property foo of value bar for example or to list all tiddlers tagged Notes.

Best wishes,

Tobias.

[Tobias Beer]

Hi again, Toddler,

Yuo may even want to consider using the schema as a prefix, should you have more than one, e.g.:

• schema
  
• schema.SomeTable
  
• schema.SomeTable.SomeField

Best wishes,

Tobias.

[Toddler Wiki]

Tobias,

Thank you for the response and the link to the schema tiddly. The way that template functions is really great. Your suggestion on changing the naming convention is also a really good one.

However, the reasoning as to why a field would its own tiddler (independent of the table) is because in this data dictionary, fields are being treated as concepts and not as subsets of a table. Let me explain... A Client_Number field will be the same whether it is on the Client_Table or the Account_Table. The Business Description or Data Type should not change, no matter what table they are in. The things that might change are whether or not that table allows nulls for that field or the fields origins, such as where it is ETLed from or when it is in a view from another table.

Furthermore, while SomeTable Notes option that you suggested can be used, it creates extra tiddlers. Using a one field to one tiddler concept creates a simple conceptual parallel to the person filling out the data dictionary.

Furthermore, using the transclude option allows an end user to print out the definition for all the columns in the table and not just the field names, which is what appears to happen with the tiddler you suggested. The template in that wiki is pretty awesome and should be commended, however, the use of the same field tiddler in multiple table tiddlers seems to be more versatile. The main crux of the issue is that when a field is used multiple times, such as Client_Number, then it would be easier to read the Client_Table definition without the Account_Table details getting in the way. While they are still pertinent, they should remain minimized until needed.

Again, I thank you for your time and showing a different way on how to accomplish this task.  However, I am still on my endeavor on figuring this out. The $reveal widget, should be the way to go. However, I do not understand how to set the state based on the tiddler name. If you or anyone else has any guidance it would be greatly appreciated.

[Tobias Beer]

Hi Toddler,

I added a few more templates to: http://schema.tiddlyspot.com

 

A Client_Number field will be the same whether it is on the Client_Table or the Account_Table.

Actually, I would think that in the client table it will probably be the primary key, perhaps ClientKey where as in other related tables it will not be, but rather be some ClientRef (pointing to the key in the client table). But sure, there can be a number of related tables all of which use ClientRef. However, you would need to have a great DB architect never to run into collisions, i.e. where a field name would always mean the same thing in all tables that have it.

Also, similar to the template I gave you, it would be quite easy for SomeTable.SomeField to check if there is any OtherTable.SomeField, so the two are intrinsically linked without having to have to be a single tiddler. You could even make it so that SomeTable.SomeField would have a field master:yes that would inidcate that this is the master tiddler for the field SomeField. So, you don't have to specify the very same details for every other table that essentially implements the same field. However, the more you have of those the less normalized your DB presumably is. So, in general, you don't even want these kind of replications, unless perhaps performance dictates you to.

 

The Business Description or Data Type should not change, no matter what table they are in.

You never know that, unless it's a strict rule in your architectural guidelines.

 

The things that might change are whether or not that table allows nulls for that field or the fields origins, such as where it is ETLed from or when it is in a view from another table.

Whatever the differences, as said, you can easily have one SomeTable.SomeField be the master, so that any OtherTable.SomeField would display a fallback from the latter for any field only defined at the master. Whether that is actually correct or not is a different issue, though. ;-)

 

Furthermore, while SomeTable Notes option that you suggested can be used, it creates extra tiddlers. Using a one field to one tiddler concept creates a simple conceptual parallel to the person filling out the data dictionary.

Not sure what you mean here: Can you provide an example?

Furthermore, using the transclude option allows an end user to print out the definition for all the columns in the table and not just the field names, which is what appears to happen with the tiddler you suggested.

You can easily extend the template to contain all kinds of details you may wish to display for those fields to a table, or for tables having the same field.

 

The template in that wiki is pretty awesome and should be commended, however, the use of the same field tiddler in multiple table tiddlers seems to be more versatile.

Again, nothing stops you from using my suggested naming conventions and still figure out what other table has a master definition for that fieldname.

 

The main crux of the issue is that when a field is used multiple times, such as Client_Number, then it would be easier to read the Client_Table definition without the Account_Table details getting in the way.

Not sure I understand this. Again, if there would be SomeTable.SomeField set to master:yes, then it would be easy to point to the master definition for that field... and indicate that it's actually some other table where that is primarily defined.

 

While they are still pertinent, they should remain minimized until needed.

Exactly, so OtherTable.SomeField may actually just be that, a tiddler with a title and nothing else. However, you'd be sure to have two tiddlers that indicate separation of concerns and you could easily have it so that OtherTable.SomeField actually has different details thant SomeTable.SomeField, should that be the case.

Again, I thank you for your time and showing a different way on how to accomplish this task.  However, I am still on my endeavor on figuring this out. The $reveal widget, should be the way to go.

I prefer the ListWidget.

However, I do not understand how to set the state based on the tiddler name. If you or anyone else has any guidance it would be greatly appreciated.

You define a state namespace via macro, e.g.:

<$vars state="$:/state/selected-table/$(currentTiddler)$">
...your stuff using a non-qualified state like like <code><$text text=<<state>>>/></code>...
</$vars>

Or, if you need to be fancy about it:

\define do-it()
<$vars state="$:/state/selected-table/$(currentTiddler)$$(qualified)$">
...your stuff using a qualified state like <code><$text text=<<state>>>/></code>...
</$vars>
\end

<$vars qualified=<<qualify>>>
<<do-it>>
</$vars>

Best wishes,

Tobias. 

[Tobias Beer]

Hi again, Toddler,

I modified the template showing other fields of the same table
to give you an example for revealing stuff using states, see...

http://schema.tiddlyspot.com

Best wishes,

Tobias.

[Toddler Wiki]

Hello Tobias,

Again, thank you for your diligent help.

Actually, I would think that in the client table it will probably be the primary key, perhaps ClientKey where as in other related tables it will not be, but rather be some ClientRef (pointing to the key in the client table). But sure, there can be a number of related tables all of which use ClientRef. However, you would need to have a great DB architect never to run into collisions, i.e. where a field name would always mean the same thing in all tables that have it.

Well a field name, especially something that would be joined, such as Client_Number would not have a different name depending on what table it is. Granted these are all architectural changes that might different from architecture to architecture.

In order to clear things up I have attached a copy of my file to this message. I hope this shows the process that I am trying to achieve. I would note the Branch_Number field and how many times it is referenced. I would like to do what you are doing in the Other Fields Of Table section but instead make it "Other Tables that Use this Field."

I tried to look at the code you provided at the bottom of your message but I am afraid that I am not savvy enough with the backend of this tool to make it work. :-/

Regards,

TW

[Tobias Beer]

Hi again, Toddler...

So, to solve your original request you basically do need to have one field-per-table tiddlers in order to filter appropriately.

In other words, you need to drop the pattern of listing all Tables (or Views) a Field is being used in the Field tiddler.

Instead, every Table.Field tiddler should describe that relation as a tiddler of its own.

...which brings you to something more resembling my suggestion, after all ;-)

Best wishes,

Tobias.