[RESOLVED] Data Design Problem - Purpose to Save Chart tool information

[webbiz]

I've watched a bunch of DB Design videos and while I'm able to follow along, I'm having trouble conceptualizing how it would apply to my needs.

Current state: You load the app, select a price data file from disk and load into a Table (T), and use that to DRAW price bars on a chart.

Future state: The user has several drawing tools at its disposal. Let's just say for brevity that there are only 5 tools (actual 18 and will eventually grow).

TOOLS
=====
TrendLine
Channels
GannLines
FanLines
Text


I'm assuming that a table needs to be created called TOOLS.

Each tool has different attributes, and some common to other tools.

Example: The TrendLine tool has the following attributes:

StartRec, StartPrice (used for the starting x/y point - record number for X, price for Y).
EndRec, EndPrice (ending x/y for line).
LineWidth
Color
Extend (boolean - for allowing line to continue off screen beyond ending x/y)


Channels Tool:
StartRec, StartPrice
EndRec, EndPrice
CloneStartRec, CloneStartPrice (for where the clone of the first line will be drawn)
LineWidth
Color


GannLines:
StartRec
EndRec
LineWidth
Color

FanLines:
StartRecA
MidRecB
EndRecC


Text:
StartRec
StartPrice
FontSize
Color


The user can draw one or more of these tools on a chart. (MANY) -- (ONE)

While many different charts can draw many different tools, each tool has its unique attribute values on each chart. In other words, you won't draw a Trendline from the same Point A to B on more than one chart. Each will be unique to that specific chart. So I don't think this would be considered 'many-to-many', right?



Do I create a Table called TOOLS and only add ONE TOOL per each kind?
Or do I create a Table called TOOLS and allow it to 'grow' by adding as many rows for a specific tool as needed for a given chart?

I'm so confused.

Or do I create a Table for every tool? Tables: TrendLines, GannLines, Channels, etc. One table for each tool added to the app.

And if that is the best route to go, something like this?

TABLE: TrendLines

TL_ID
CHART_ID (chart it is assigned to)
START_REC
START_PRICE
END_REC
END_PRICE
LINEWIDTH
COLOR
EXTEND


When the app is started, the user can select a directory or it will open the most recent. A Table (MASTER) is created that has (ID PK, StockName Txt, StockSymbol Txt, FirstDate, LastDate, FieldCount).
A recordset (RsMaster) is created holding all the stock names, and a combobox is filled with these names.

The MASTER table changes depending on the directory selected. So using the ID of the Table (Master) will not work as the Chart_ID.

StockNames and StockSymbols are UNIQUE to the data file itself. And only 1 chart can be open at a time in this app, so I don't have to worry about there being two IBM charts with different tools drawn.

Thus, either StockNames or StockSymbols (or a combination of both) should be good enough to be CHART_ID for the tools.

So when the user opens a chart that has a name/symbol (CHART_ID) that are found with some tools in the TOOL table(s), I could query all the attributes and redraw the tools that were saved to the DB for later redraw. If the user deletes a tool, to be able to quickly delete from the Table.


I'll stop here. Hoping some here have some pointers on how best to design this.


PS: Something else I'm curious about. There are thousands of stocks. A user might load a stock and draw on it just once, saving the tool data to DB. It could sit there for 1000 years and never retrieved again. I'm thinking that I'll need to create some kind of 'cleanup' query later on that lists all saved Charts that had tools saved for them allowing the user to pick which to remove, etc. That's down the road but I mention it in case knowing that helps in determining how to structure the DB.

TIA

(DB = RC6 SQLite)

[jmcilhinney]

Thread moved to Database Development forum.

[sapator]

It's too early so I didn't read this top to bottom but what I would do is create a new table for tools and new table for tools attributes and not sure I caught the last one. Does tool have TrendLines or TrendLines are the attributes?
Anyhow you go tool and attributes and you do a one to many there. One tool many attributes.

[Zvoni]

I remember at the beginning giving you an idea how i would do it:
All UNIQUE Attributes of a Tool conglomerated into a single JSON-Field

[webbiz]

I remember at the beginning giving you an idea how i would do it:
All UNIQUE Attributes of a Tool conglomerated into a single JSON-Field

I don't recall the prior discussions (too long ago) and my searches come up dry. In addition, a completely different mind-space today then a year or so ago as 'finally' my app (and testapp) are DB SQL-centric which it was not before. Now there is an existing table "T" (and of course TW and TM).

So working from the basis of having table (T) and (MASTER), I'm focused on how to design the tool data save.

[sapator]

Naming the tables TW and TM is bad practice.
Name them accordingly.
What are some contents of table TM and TW (I assume table T = Tools) and how do they relate to tools and each other?

[Zvoni]

Naming the tables TW and TM is bad practice.
Name them accordingly.
What are some contents of table TM and TW (I assume table T = Tools) and how do they relate to tools and each other?

Eh...no..
TW = Data Weekly View
TM = Data Monthly View

I don't recall the prior discussions (too long ago) and my searches come up dry. In addition, a completely different mind-space today then a year or so ago as 'finally' my app (and testapp) are DB SQL-centric which it was not before. Now there is an existing table "T" (and of course TW and TM).

So working from the basis of having table (T) and (MASTER), I'm focused on how to design the tool data save.

How you want to store your Tools has nothing to do with your Data.
The Data is just "there" in whatever Format

The "Tools" are used to DISPLAY your Data.

As always: There is a difference how something is stored and how something is displayed.
Usually, those two have no relationship with each other whatsoever, as in: the displaying being dependant HOW something is stored

Your "Tools" have an "interface" ("For this Tool i need those Fields, for that Tool i need those Fields" etc.)

Your Data have an "Interface", too (Columns ID, tDate and so on)

Your Job is to create a "middle-layer" in between to grab the Data from your Database, and transform/present it to the chosen Tool(s) according to the Tool's "Interface"

OK, accepted, that your mindset has progressed from a year ago.
I could see a solution being a "m:m"-setup

Table TOOLS
ID
Description (e.g. GannLines)

Table Attributes
ID
Description (e.g. StartRec)

Table Tools_Attributes
Tools_ID --> ForeignKey to TOOLS
Attributes_ID --> ForeignKey to Attributes
Chart_ID --> ForeignKey to Charts

[webbiz]

It's too early so I didn't read this top to bottom but what I would do is create a new table for tools and new table for tools attributes and not sure I caught the last one. Does tool have TrendLines or TrendLines are the attributes?
Anyhow you go tool and attributes and you do a one to many there. One tool many attributes.

Trendlines is a tool. It's simply a segment. If attribute 'extend' is true, then it becomes a ray.

So what you'd do is create a table called TOOLS with what fields?

TOOLS
=======
Tool_ID



TOOL_ATTR
========
TA_ID
Pt1_Rec
Pt1_Price
Pt2_Rec
Pt2_Price
Pt3_Rec
Pt3_Price
LineWidth
Color
Extend


CHART
======
Chart_ID
Tool_ID
TA_ID


So we talking about a Table (CHART) that holds 'saved' chart names (Chart_ID) and can have many tools (Tool_ID)?

Chart_ID Tool_ID
=============
AAPL 2
AAPL 2
AAPL 2
AAPL 4
AAPL 7
IBM 2
IBM 6
IBM 6
IBM 6


Something like this?

[Zvoni]

Anyhow you go tool and attributes and you do a one to many there. One tool many attributes.

Uh, definitely no!
It's "m:m"
I definitely see mutiple "Color", "StartRec" and so on

[webbiz]

Naming the tables TW and TM is bad practice.
Name them accordingly.
What are some contents of table TM and TW (I assume table T = Tools) and how do they relate to tools and each other?

No, table (T) is for daily TABLE. TW is weekly TABLE converted from (T), and TM is monthly TABLE converted from (T).

These tables hold the price data in three time frames.

These are recreated each time the app is started and when the user chooses a new data file to load. Thus, they are not permanent.



As for the TOOLS, they will be a DB file on drive and not in memory. They must be persistent so when a data file that was loaded a previous time that had tools drawn on it can have those tools redrawn on it.


:-)

[Zvoni]

errr...wrong

Table TOOLS
ID
Description (e.g. GannLines)

Table Attributes
ID
Description (e.g. StartRec)

Table Tools_Attributes
Tools_ID --> ForeignKey to TOOLS
Attributes_ID --> ForeignKey to Attributes
Chart_ID --> ForeignKey to Charts

Table TOOLS
ID
Description (e.g. GannLines)

Table Attributes
ID
Description (e.g. StartRec)

Table Tools_Attributes
Tools_ID --> ForeignKey to TOOLS
Attributes_ID --> ForeignKey to Attributes


Chart_Tools
Tool_ID --> ForeignKey to Tools
Chart_ID --> ForeignKey to Charts

Remember: the "m:m"-table Tools_Attributes describes the "make up" of "a" Tool.

[webbiz]

Eh...no..
TW = Data Weekly View
TM = Data Monthly View


How you want to store your Tools has nothing to do with your Data.
The Data is just "there" in whatever Format

The "Tools" are used to DISPLAY your Data.

As always: There is a difference how something is stored and how something is displayed.
Usually, those two have no relationship with each other whatsoever, as in: the displaying being dependant HOW something is stored

Your "Tools" have an "interface" ("For this Tool i need those Fields, for that Tool i need those Fields" etc.)

Your Data have an "Interface", too (Columns ID, tDate and so on)

Your Job is to create a "middle-layer" in between to grab the Data from your Database, and transform/present it to the chosen Tool(s) according to the Tool's "Interface"

OK, accepted, that your mindset has progressed from a year ago.
I could see a solution being a "m:m"-setup

Table TOOLS
ID
Description (e.g. GannLines)

Table Attributes
ID
Description (e.g. StartRec)

Table Tools_Attributes
Tools_ID --> ForeignKey to TOOLS
Attributes_ID --> ForeignKey to Attributes
Chart_ID --> ForeignKey to Charts

It's going to take a bit of playing with this to get my thinking into a DB way of thinking. It's pretty alien to me.

TOOLS
Data is included here just so I can visualize what this would result in.

ID	Description
1	TrendLine
2	GannLines

ATTRIBUTES

ID	Description
1	X1_Rec
2	Y1_Price
3	X2_Rec
4	Y2_Price
5	X3_Rec
6	Y3_Price
7	Color

Creating two tables like this seems easy enough. Assume there are only 2 tools and 7 possible attributes, the tables above would be COMPLETE. That's all the data they would contain. Just ID numbers and descriptions. Correct?

So how/where do I store the VALUES for those attributes?

And where is Chart_ID coming from? Table (T) contains StockNames and StockSymbols. Is there another table that looks like this?

Chart_ID	StockName	Symbol
1	Apple	AAPL
2	Microsoft	MSFT
3	Tesla	TSLA

[Zvoni]

It's going to take a bit of playing with this to get my thinking into a DB way of thinking. It's pretty alien to me.

TOOLS
Data is included here just so I can visualize what this would result in.

ID	Description
1	TrendLine
2	GannLines

ATTRIBUTES

ID	Description
1	X1_Rec
2	Y1_Price
3	X2_Rec
4	Y2_Price
5	X3_Rec
6	Y3_Price
7	Color

Creating two tables like this seems easy enough. Assume there are only 2 tools and 7 possible attributes, the tables above would be COMPLETE. That's all the data they would contain. Just ID numbers and descriptions. Correct?

So how/where do I store the VALUES for those attributes?

Are the Values per Chart? or per Tool?

If per Tool
Create a "mixing"-table
Table Tools_Attributes
Tool_ID --> FK to Tools
Attribute_ID --> FK to Attributes
Value --> Whatever Datatype. Your Best probably TEXT. You'll know if it's all integers, doubles, or whatever

Then you still need a mixing table to connect Chart with Tools
Tool_ID --> FK to Tools
Chart_ID --> FK to Charts

And where is Chart_ID coming from? Table (T) contains StockNames and StockSymbols. Is there another table that looks like this?

Chart_ID	StockName	Symbol
1	Apple	AAPL
2	Microsoft	MSFT
3	Tesla	TSLA

If the Values for your Tool is per Chart
Create a "mixing"-table
Table Tools_Attributes_chart
Tool_ID --> FK to Tools
Attribute_ID --> FK to Attributes
Chart_ID --> FK to Charts
Value --> Whatever Datatype. Your Best probably TEXT. You'll know if it's all integers, doubles, or whatever

Just grab the Chart_ID from Charts, since i guess the StockSymbol is UNIQUE

Whatever combination above: Remember to set all ForeignKeys TOGETHER to UNIQUE.
That is if per Chart a Tool is only allowed once (what i guess)

[webbiz]

Are the Values per Chart? or per Tool?

If per Tool
Create a "mixing"-table
Table Tools_Attributes
Tool_ID --> FK to Tools
Attribute_ID --> FK to Attributes
Value --> Whatever Datatype. Your Best probably TEXT. You'll know if it's all integers, doubles, or whatever

Then you still need a mixing table to connect Chart with Tools
Tool_ID --> FK to Tools
Chart_ID --> FK to Charts




If the Values for your Tool is per Chart
Create a "mixing"-table
Table Tools_Attributes_chart
Tool_ID --> FK to Tools
Attribute_ID --> FK to Attributes
Chart_ID --> FK to Charts
Value --> Whatever Datatype. Your Best probably TEXT. You'll know if it's all integers, doubles, or whatever

Just grab the Chart_ID from Charts, since i guess the StockSymbol is UNIQUE

Whatever combination above: Remember to set all ForeignKeys TOGETHER to UNIQUE.
That is if per Chart a Tool is only allowed once (what i guess)

I'm sorry, but I'm confused to standstill (hopefully temporarily).


Let's just focus on ONE TOOL to sort things out.



Suppose you load up a chart of APPLE (AAPL). Yes, StockSymbols are unique. StockNames mostly so, but some companies have multiple symbols with similar names, so symbols are better unique.


Now AAPL is displayed. You select the TRENDLINE tool and draw a line from x1/y1 to x2/y2.

But then you add another line from a different x1/y1 to x2/y2.

You can add as many as you like.

So I don't know how to answer your question about (Values per Chart or per Tool).

Here I have ONE CHART. I have say THREE TRENDLINES. And each trendline has its own VALUES.

So you can have different values per tool, and different values per chart because you would likely draw the lines differently on a different chart.

How do I answer that question?

[sapator]

Uh, definitely no!
It's "m:m"
I definitely see mutiple "Color", "StartRec" and so on

One Channels Tool: many GannLines .
Another Channels Tool: many GannLines .
I meant.

Also naming table TW and TD is UTTERLY and COMPLETELY WRONG.
I have a database with 200 tables. I should just quit now and get another job if all the tables where named something like:
T1, T2, TW,TV,TV,BB,XX,YY.

[sapator]

TrendLine --> many attributes on and intermediate table Terntlineid -- AttributeID1,Terntlineid -- AttributeID2 etc
Chart_ID i don't know it seems not relating to something.

[webbiz]

One Channels Tool: many GannLines .
Another Channels Tool: many GannLines .
I meant.

Also naming table TW and TD is UTTERLY and COMPLETELY WRONG.
I have a database with 200 tables. I should just quit now and get another job if all the tables where named something like:
T1, T2, TW,TV,TV,BB,XX,YY.

The ONLY tables that are named T, TW, TM are the InMemory-DB price data tables.

The naming started based on the core testapp Olaf posted in the CodeBase. There was just one table...'T'.

But now that I'm expanding this app to include more tables, those additional tables will have distinctive names. But keeping 'T', 'TW' and 'TM' does not see like a problem to describe the only tables that are InMemory.


As to...

One Channels Tool: many GannLines etc., I don't follow.

Channels is tool.
GannLines is a tool.

Tools have no association with EACH OTHER.

They are separate entities.

A chart (Entity) can have 0 or more Channels, 0 or more GannLines, etc.

The chart below has a GannLines tool and a Channel tool applied to British Pound.

[sapator]

But now that I'm expanding this app to include more tables, those additional tables will have distinctive names. But keeping 'T', 'TW' and 'TM' does not see like a problem to describe the only tables that are InMemory.

Ye if only I had a dime whenever I listened to that.

Anyhow I meant the tool attributes
StartRec
EndRec
LineWidth
Color
On whatever table you have them in.

[webbiz]

Ye if only I had a dime whenever I listened to that.

Anyhow I meant the tool attributes
StartRec
EndRec
LineWidth
Color
On whatever table you have them in.

Right now I don't have them in any table cuz I'm still trying to sort this table thing out.

At this moment I don't see how these come together to allow saving Tool information for a specific chart.

[Zvoni]

I'm sorry, but I'm confused to standstill (hopefully temporarily).


Let's just focus on ONE TOOL to sort things out.



Suppose you load up a chart of APPLE (AAPL). Yes, StockSymbols are unique. StockNames mostly so, but some companies have multiple symbols with similar names, so symbols are better unique.


Now AAPL is displayed. You select the TRENDLINE tool and draw a line from x1/y1 to x2/y2.

But then you add another line from a different x1/y1 to x2/y2.

You can add as many as you like.

So I don't know how to answer your question about (Values per Chart or per Tool).

Here I have ONE CHART. I have say THREE TRENDLINES. And each trendline has its own VALUES.

So you can have different values per tool, and different values per chart because you would likely draw the lines differently on a different chart.

How do I answer that question?

Ok, so the values are per Chart.
and since you allow one tool to be used more than once, i have to think about it

[webbiz]

Ok, so the values are per Chart.
and since you allow one tool to be used more than once, i have to think about it

What about something like this?

CHARTS TABLE

Chart_ID (PK)	StockName CHAR	StockSymbol CHAR

TRENDLINES TABLE

TL_ID (PK)	Chart_ID (FK)	P1_Date TEXT	P1_Price REAL	P2_Date TEXT	P2_Price REAL	Ray BOOL	Color INT	LineWidth INT

And there would be a table for each different tool available in the app. Each tool table can then contain just the attributes specific to that tool.


GANNLINES TABLE

GL_ID (PK)	Chart_ID (FK)	P1_Date TEXT	P2_Date TEXT	Color INT

This seems like a clear format to me. But I'm the novice in DB so I don't know if this is the wise way to go.

??

[techgnome]

I'd have a tool for ToolTypes ... then another table ChartTools... which has a foreign key to the ToolType and to the Chart ... it then has a third field Attributes, which contains serialized (in json format) data for that tool/chart ... and that's it... no need to complicate it with excess tables or fields. That way if you expand additional attributes, you don't need to chane the tables ... or worry about how tool A has one set of attributes, and tool b has something completely different and Tool C has some of A and some of B...


-tg

[Zvoni]

I'd have a tool for ToolTypes ... then another table ChartTools... which has a foreign key to the ToolType and to the Chart ... it then has a third field Attributes, which contains serialized (in json format) data for that tool/chart ... and that's it... no need to complicate it with excess tables or fields. That way if you expand additional attributes, you don't need to chane the tables ... or worry about how tool A has one set of attributes, and tool b has something completely different and Tool C has some of A and some of B...


-tg

exactly what i said in post 4

[webbiz]

I'd have a tool for ToolTypes ... then another table ChartTools... which has a foreign key to the ToolType and to the Chart ... it then has a third field Attributes, which contains serialized (in json format) data for that tool/chart ... and that's it... no need to complicate it with excess tables or fields. That way if you expand additional attributes, you don't need to chane the tables ... or worry about how tool A has one set of attributes, and tool b has something completely different and Tool C has some of A and some of B...


-tg

@Zvoni Yes, you did say this. But again, I don't know what was discussed back when I didn't know SQL from JILL.

@techgnome @Zvoni

I don't understand the reference to "serialized" JSON.

According to Gooogle, serialized JSON is the process of converting a JavaScript object into a JSON string.

What JavaScript object am I going to be converting?


As per @techgnome table suggestions:

Table TOOLTYPES
(What does this hold?)


Table CHARTTOOLS
(What does this hold?)


ToolTypes hold the names of tools, right? TrendLine, Channel, GannLines, etc.

Which table has the 3rd field for JSON?

ChartTools holds the names of charts and the ID's of ToolType?


If you can give a guy a leg up on this in newbie terms, I'd appreciate it.

Meanwhile, I guess I'm off to watch videos on JSON now. ooooooh....

[sapator]

It depends on your needs. You don't HAVE to use Json and be aware that it in not supported on older SQL Servers.
If you don't have a good grasp you might end up with worse results than table naming.
So it's up to you.
Here is an example:
https://learn.microsoft.com/en-us/sq...l-server-ver16

[webbiz]

CHARTS TABLE

Chart_ID (PK)	StockName CHAR	StockSymbol CHAR

TOOLTYPES TABLE

Tool_ID (PK)	Tool_Type CHAR

CHARTTOOLS TABLE

Chart_ID (FK)	Tool_ID (FK)	JSON

Like this?

[webbiz]

It depends on your needs. You don't HAVE to use Json and be aware that it in not supported on older SQL Servers.
If you don't have a good grasp you might end up with worse results than table naming.
So it's up to you.
Here is an example:
https://learn.microsoft.com/en-us/sq...l-server-ver16

Thanks @sapator

I won't have to worry about older SQL Servers.

This is a SQLite3 DB inside a VB6 application.

[techgnome]

It depends on your needs. You don't HAVE to use Json and be aware that it in not supported on older SQL Servers.
If you don't have a good grasp you might end up with worse results than table naming.
So it's up to you.
Here is an example:
https://learn.microsoft.com/en-us/sq...l-server-ver16

Doesn't have to be supported. You just need a text field that's big enough. That's what we're doing in our app... we have an object structure, serialize it to a json string, then stuff the string into a CLOB field in the Oracle database.

But yes... like any other db design, it does need to be understood and you do need to have some grasp of things...


-tg

[sapator]

This is for another discussion but if it's not supported and we are parsing text then you might get to overhead. For example doing a 'like'.
In our company we still have 2012R servers, so we are patiently waiting in some JSON inputs, to upgrade to a new server. But as this is SQLite, I have no clue but I see that some versions support Json some not...

[Zvoni]

I got an idea how to skin this cat without having to use JSON or individual tables per tool
but will have to wait till Monday

[Zvoni]

Right, here we go.
I'm sure i'm going to receive flak for this, but hey, it solves the initial problem

1) You still have the Table "Tools"
with
ID --> Primary Key
Description --> e.g. "GannLine"

ID	Description
1	GannLine

2) You still have the Table "Attributes", which is just a "List" of all available Attributes, independent of the Tool
with
ID (Integer)--> Primary Key
Description (TEXT)--> e.g. "StartRec", "Color" etc.
DataType (TEXT)--> e.g. "TEXT", "REAL", "INTEGER" -->

ID	Description	DataType
1	StartRec	TEXT
2	EndRec	TEXT
3	LineWidth	INTEGER
4	Color	INTEGER

3) You still have the "m:m"-Mix table "Tools_Attributes" which DESCRIBES the MAKE-UP of a Tool
with
(Optional: ID --> Primary Key)
Tool_ID --> Foreign Key to Tools
Attribute_ID --> Foreign Key to Attributes

Tool_ID	Attribute_ID
1	1
1	2
1	3
1	4

4) Now the "Fun" starts
You have the Following Table "Tool_Chart_Attributes"
with
(Optional: ID (Integer) --> Primary Key)
Tool_ID
Chart_ID
(Optional: DisplayName (Text))
.... AND....
For each "Entry" in Attributes, you have a Column with Exactly the same Name as the corresponding "Entry" in Attributes
Might look like this (if you have, say, 32 possible Attributes in total, you have a Column for each of those 32 entries)
Here you actually store the Values!!

Tool_ID	Chart_ID	DisplayName	StartRec	EndRec	LineWidth	Color	FontSize	StartPrice	EndPrice
1	1	GannLine 1	2023-09-01	2023-09-18	5	123456	10
1	1	GannLine 2	2023-08-01	2023-08-31	5	456789	8

Now, how to Grab the Values from only those Columns you need:
4a) Grab the Attributes from the Tool_Attributes Make-Up-Table

SELECT group_concat(A.Description, ', ') As Fields FROM Attributes As A 
INNER JOIN Tools_Attributes AS TA ON TA.Attributes_ID=A.ID 
INNER JOIN Tools AS T ON T.ID=TA.Tools_ID 
WHERE T.ID=1 /* <-- Or pass a Paramater */
/*WHERE T.ID = ? */
GROUP BY T.ID

You'll get a single Recordset with a single Field back with something like
"StartRec, EndRec, LineWidth, Color"

4b) Now you USE this in the Following SQl

Dim MyRS As Recordset
Dim SQL As String
'Execute SQL in step 4a 
SQL = "SELECT Tool_ID, Chart_ID, DisplayName, " & MyRS("Fields") 
SQL = SQL & " FROM Tool_Chart_Attributes WHERE Tool_ID=? AND Chart_ID=?"

and voila: no need for separate "Tool"-Tables, no need for JSON

If you add/change/remove an attribute, it's pretty simple to ALTER the Table "Tool_Chart_Attributes" to add or remove a Column
--> That's why there is "DataType"-Column in Attributes
IIRC, you would have to first drop the foreign-Key constraint ("Pragma foreign_keys=off"), add/change/remove a column, then activate foreign keys it again

[webbiz]

Right, here we go.
I'm sure i'm going to receive flak for this, but hey, it solves the initial problem



4b) Now you USE this in the Following SQl

Dim MyRS As Recordset
Dim SQL As String
'Execute SQL in step 4a 
SQL = "SELECT Tool_ID, Chart_ID, DisplayName, " & MyRS("Fields") 
SQL = SQL & " FROM Tool_Chart_Attributes WHERE Tool_ID=? AND Chart_ID=?"

and voila: no need for separate "Tool"-Tables, no need for JSON

If you add/change/remove an attribute, it's pretty simple to ALTER the Table "Tool_Chart_Attributes" to add or remove a Column
--> That's why there is "DataType"-Column in Attributes
IIRC, you would have to first drop the foreign-Key constraint ("Pragma foreign_keys=off"), add/change/remove a column, then activate foreign keys it again

I read this quote somewhere "Code is like a joke: If you have to explain it, it's bad".

Maybe it was once true, but some folks need explanations regardless of code quality.


If only I was fluent in SQL-speak. Since I'm not, I cannot determine which is the better approach, JSON or NOT.

What I can say is that at the moment, I can understand JSON.

With that said, I can follow the visuals provided and it looks like it would be in the right direction.

The CHART_TOOLS table (this is the "Tool_Chart_Attributes" table) is where the action is.

This table will have rows added and deleted quite regularly. No need for ID (PK).

Now here is where my table ignorance really shines.

Each tool has different attributes (some are the same), and different number of attributes.

When you add different tool attributes to the CHART_TOOLS, is this table 'expanding' when tools with more attributes than those already in the table are added?

And what happens when the tools with the most attributes are removed, like when the user deletes the tool from the chart? Does the table shrink to the size of the next largest amount of attributes?

Thanks @Zvoni

[sapator]

Since Zvoni is asking for it.
You can have only one table and one column.
That is if you pass all the information in an Json object.
So no need for long explanations no need for inner joins no need for long queries (well maybe so you can break the Json down) and table maintenance.

[Zvoni]

With that said, I can follow the visuals provided and it looks like it would be in the right direction.

The CHART_TOOLS table (this is the "Tool_Chart_Attributes" table) is where the action is.

This table will have rows added and deleted quite regularly. No need for ID (PK).

Now here is where my table ignorance really shines.

Each tool has different attributes (some are the same), and different number of attributes.

When you add different tool attributes to the CHART_TOOLS, is this table 'expanding' when tools with more attributes than those already in the table are added?

No. OUT OF THE GATES, the table CHART_TOOLS consists of Columns for ALL POSSIBLE Attributes, that might be USED for any given TOOL.
This Table only "expands/shrinks" when you add an Attribute, which is completely (!) new, irrespective in which Tool it appears, or you remove an Attribute in "general" (e.g. you decide that the Attribute "Color" is to removed from any Tool)

And what happens when the tools with the most attributes are removed, like when the user deletes the tool from the chart? Does the table shrink to the size of the next largest amount of attributes?

Thanks @Zvoni

No, see above. CHART_TOOLS is, basically, a "template" offering you all possible Fields. You just USE those Fields, you need for any given Tool.
And the Fields you NEED you get from the Make-Up Table

[webbiz]

Since Zvoni is asking for it.
You can have only one table and one column.
That is if you pass all the information in an Json object.
So no need for long explanations no need for inner joins no need for long queries (well maybe so you can break the Json down) and table maintenance.

To be clear, @Zvoni twice suggested the JSON approach some time ago. This recent non-JSON discussion is most likely an academic one, to consider how it can be done without JSON.

And as for the test app I'm working on, I have decided on JSON if not for the clarity but for the experience. And I have a soft spot for files that can be read with an ascii text editor.

[Zvoni]

To be clear, @Zvoni twice suggested the JSON approach some time ago. This recent non-JSON discussion is most likely an academic one, to consider how it can be done without JSON.

And as for the test app I'm working on, I have decided on JSON if not for the clarity but for the experience. And I have a soft spot for files that can be read with an ascii text editor.

Yes, it is kind of academic.
But i consider "Ideas", "possible Solutions", "Options" to be like "Ammunition": Better have it and not needing it, than the other way around.

And this way we do get a "visual" Representation of the "possible Solution", if someone else looks for something like this

[webbiz]

Yes, it is kind of academic.
But i consider "Ideas", "possible Solutions", "Options" to be like "Ammunition": Better have it and not needing it, than the other way around.

And this way we do get a "visual" Representation of the "possible Solution", if someone else looks for something like this

Completely agree. Appreciated the time you provided. Cheers!