[solved]  Best practise - foreign key versus own field
lazarus / 16-Databases

----------------------------------------------------------------------
Nicole · 2024-01-29 09:36

On reconstucting my database I wonder this:

I had a table
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBKALENDER(  JDATUM date NOT NULL,....  CONSTRAINT PK_TBKALENDER
PRIMARY KEY (JDATUM));
Under the line the table holds not much more than the date.
This date I used as foreign - key

Where did I use it?
In quotes: e.g. stock-quotes hold a lot of date-information every day.
These I addressed as foreign key and the pointed at the above table.

I wonder:
Is this a good idea at all?
Is the date with 32 bit (?) not the same as the foreign key in matters
of performance and storage?
The disadvantage of the foreign-key construction is, that I need 2
tables instead of one. Every change may be harder.

My second table of a foreign key is:
"pointing at a Char(2)"
I had a list of two digit chars, which was not too long. At this list
pointed a foreign key of other tables.
Would you recommend to generate a char(2) field instead of using a
foreign key?

----------------------------------------------------------------------
Zvoni · 2024-01-29 09:44

Quote from: Nicole on January 29, 2024, 10:36:53 amOn reconstucting my
database I wonder this:

I had a table
Code: MySQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE TBKALENDER(  JDATUM date NOT NULL,....  CONSTRAINT PK_TBKALENDER
PRIMARY KEY (JDATUM));
Under the line the table holds not much more than the date.
This date I used as foreign - key

Where did I use it?
In quotes: e.g. stock-quotes hold a lot of date-information every day.
These I addressed as foreign key and the pointed at the above table.

I wonder:
Is this a good idea at all?
Is the date with 32 bit (?) not the same as the foreign key in matters
of performance and storage?
The disadvantage of the foreign-key construction is, that I need 2
tables instead of one. Every change may be harder.

My second table of a foreign key is:
"pointing at a Char(2)"
I had a list of two digit chars, which was not too long. At this list
pointed a foreign key of other tables.
Would you recommend to generate a char(2) field instead of using a
foreign key?
I'm trying to wrap my mind around that.
You do realize, with JDATUM being a Primary Key, you can only add one
record per JDATUM (and since it's of Type "date") resp. per Day.

How is that supposed to work with Stock-Data?

Or is that table meant to be a "Calendar"-Table as a Master to a
Child-Table, say "Transactions" (or whatever)?

In any case, i never use Date(Time) Columns as Primary or Foreign Key,
since the memory-requirement is miniscule and just adds complexity
If i need a Date(Time) in a Child-Table ("Transactions") it's its own
Field.
In subsequent Queries it would then be just another Field i can filter
or Group by or whatever

EDIT: After reading and participating in your other Threads, maybe you
should show us the relevant tables (CREATE-Table-Statements), their
Columns, and their relations between them, AND SPECIFICALLY: What they
are meant to REPRESENT
Maybe we can crack this nut better then.
Since you admitted, this is an "old" design" of your Database, and you
admitted to a "lack" of Database-Knowledge......

----------------------------------------------------------------------
Nicole · 2024-01-29 10:27

As I wrote this database about a decade ago, I hardly could spell the
word database.
The mistakes I made at my first database-attempt trouble me today.
So I am going to re-organize it. And after it I will forget all about
databases for the next decade.

This table holds more than the date, but those fields are not crucial
for the question, which is:
Is it better to have a date as field or as foreign key?

----------------------------------------------------------------------
alpine · 2024-01-29 10:43

Quote from: Nicole on January 29, 2024, 11:27:16 amThis table holds
more than the date, but those fields are not crucial for the question,
which is:
Is it better to have a date as field or as foreign key?
Now I'm having a trouble wrappin my mind around that.  

First of all, foreign keys are mechanism for ensuring data referential
integrity (aka DRI). Logically, you're not obliged to use them to
relate two tables e.g. in JOINs.
And DATE and DATETIME are quite unusual type (for me) to make a PK/FK
relations, unless they're not part of a longer segmented key.

----------------------------------------------------------------------
Nicole · 2024-01-29 11:53

sorry for the misunderstanding.
My question was not, if the date is an appropriate primary key, but if
it is better to put a foreign key or a date as field.
So it is about the foreign key of the SECOND table.

To illustrate this, I write my two tables as example:

TABLE 1:
id_table1 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = 1.1.2024


TABLE 2 - version one:
id_table2 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = 1.1.2024   <------------------

TABLE 2 - version two:
id_table2 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = foreign key 1 field mydate of table 1 <------------------

Which version of table 2 field myDate is better style?

----------------------------------------------------------------------
Zvoni · 2024-01-29 12:13

Quote from: Nicole on January 29, 2024, 12:53:23 pmsorry for the
misunderstanding.
My question was not, if the date is an appropriate primary key, but if
it is better to put a foreign key or a date as field.
So it is about the foreign key of the SECOND table.

To illustrate this, I write my two tables as example:

TABLE 1:
id_table1 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = 1.1.2024


TABLE 2 - version one:
id_table2 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = 1.1.2024   <------------------

TABLE 2 - version two:
id_table2 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = foreign key 1 field mydate of table 1 <------------------

Which version of table 2 field myDate is better style?
If Table1 is the one which has JDATUM as a Primary Key, then there is
only one record per Day

In Table2 Version 1 Mydate would be a regular Field, not a Foreign Key
In Table2 Version 2 The Foreign Key should point to id_table1 (if that
Field actually becomes a Primary Key), not MyDate, because if you
point it to Table1.MyDate you're back at Table2 Version 1

Everything said: I cannot answer which is better, since we don't know
WHAT Data is in what table.
Just by your description i can't determine which is Master-Data, and
which is Child-Data.
We'd need some concrete examples.

----------------------------------------------------------------------
alpine · 2024-01-29 12:26

Quote from: Nicole on January 29, 2024, 12:53:23 pmsorry for the
misunderstanding.
My question was not, if the date is an appropriate primary key, but if
it is better to put a foreign key or a date as field.
So it is about the foreign key of the SECOND table.

To illustrate this, I write my two tables as example:

TABLE 1:
id_table1 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = 1.1.2024


TABLE 2 - version one:
id_table2 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = 1.1.2024   <------------------

TABLE 2 - version two:
id_table2 = 1
fieldA = some value
fieldB = another value
filedC = third value
mydate = foreign key 1 field mydate of table 1 <------------------

Which version of table 2 field myDate is better style?
It is not a matter of style.

In the 1-st version you'll have the advantage to query only one table
(not two) if you want to filter/order TABLE2 by mydate. Zvoni
mentioned that already somewhere in all the threads spawned.
In the 2-nd you'll have less redundancy of data, perhaps, but you must
impose some CHECK constraint, since most probably it is needed by the
business rules.

IMHO all people that are trying to help will be happy to receive more
complete description of your database perhaps with some explanations.
Probably everything will be cracked in a matter of minutes instead of
writing endless advises which obviously were not clearly understood.

----------------------------------------------------------------------
Joanna · 2024-01-29 12:33

I’m curious if maybe the  table 2  contains unique dates but table 1
can have multiple rows with same dates ? I’ve used dates in one table
as sort of an index for another table before.

----------------------------------------------------------------------
Nicole · 2024-01-29 12:35

As I do not know, what is master-data and what child-data, I am not
sure, how to answer.

The first table holds all days of a certain period, some with
information, some without. e.g.  a certain code may say "this was a
holiday".
The second table holds some data of the same period which you can
imaging similar as stock-quotes, as date, open, high, low, close.


table 1 must not contain any date twice
table 2  must not contain any date twice in combination with a certain
second field.

----------------------------------------------------------------------
Zvoni · 2024-01-29 12:46

Quote from: Nicole on January 29, 2024, 01:35:59 pmAs I do not know,
what is master-data and what child-data, I am not sure, how to answer.

The first table holds all days of a certain period, some with
information, some without. e.g.  a certain code may say "this was a
holiday".
The second table holds some data of the same period which you can
imaging similar as stock-quotes, as date, open, high, low, close.


table 1 must not contain any date twice
table 2  must not contain any date twice in combination with a certain
second field.
Now we're getting somewhere.
Gut feeling: Go with Table2 version2, BUT as i wrote: Foreign Key
points to id_table1, not MyDate, with id_table1 being an Auto-ID
integer Primary Key
As for your "Constraint" in table2: Leave that thought "table 2  must
not contain any date twice in combination with a certain second
field."
You ARE actually allowing a Date to be twice (or more times) in that
table, but as you said: In combination with another Field.
So you'd still need a UNIQUE-Constraint on those two Fields.

After thinking about it: This sounds more and more like an actual
"m:m"-relation
One side being the Date, the other side being that ominous "other"
second field

Could we have some actual data of both tables? and a description of
what they represent?

----------------------------------------------------------------------
Nicole · 2024-01-29 13:21

The chaos increases by asking for information, which seems not to be
needed.
e.g. I posted the example with an extra primary key, only to avoid the
off-topic discussion if the date is a good primary key. 

If there is no answer, more information would not change it.
I my table the date IS the primary key.

My question was,
Is it better to save the date as a field or as a foreign key?

The answer may be very tricky, because the reason may be efficiency in
storage and statements of the database.

----------------------------------------------------------------------
Zvoni · 2024-01-29 13:28

Quote from: Nicole on January 29, 2024, 02:21:16 pmThe chaos increases
by asking for information, which seems not to be needed.
e.g. I posted the example with an extra primary key, only to avoid the
off-topic discussion if the date is a good primary key. 

If there is no answer, more information would not change it.
I my table the date IS the primary key.

My question was,
Is it better to save the date as a field or as a foreign key?

The answer may be very tricky, because the reason may be efficiency in
storage and statements of the database.

Well then... in that case: IMO it doesn't make that much difference
storage-wise (4/8 bytes for an Integer vs. 4/8 Bytes for a Date/Time).
The Difference is, if Firebird can create an efficient Index for it,
which can speed up joins and filters by a factor you wouldn't believe.
For (Auto) Integer Primary Keys IT IS proven, that the created indices
work as advertised, for anything else it's anyone's guess

That said: If your "date" is really a primary key (UNIQUE and NOT
NULL), then the Indices should work as well, because a Date/Time is
still just an Integer (If only Date, Double if with Time) under the
hood

----------------------------------------------------------------------
rvk · 2024-01-29 13:50

QuoteThat said: If your "date" is really a primary key (UNIQUE and NOT
NULL), then the Indices should work as well, because a Date/Time is
still just an Integer (If only Date, Double if with Time) under the
hoodYes. In mentioned example you can use DATE as PK.
But you also need to consider if you are not taking the normalization
to an extreme.

If DATE is the only field in that table and it is referenced by for
example a QUOTE table. And that QUOTE table only has ONE field
pointing to that DATE table, you need to consider if it's not better
to just merge the DATE field into the QUOTE table (as QUOTE_DATE). Why
create a foreign key to a table with just one single DATE field
(because DATE field takes as much space as the foreign key itself).

If you would do this with a VARCHAR field (which is duplicated a lot)
then you would have a point there. But for a DATE field I would just
add it to the main table. Just my opinion...

----------------------------------------------------------------------
Joanna · 2024-01-29 15:18

@rvk I think two separate tables are needed to represent what happened
on a particular date. So in one table the date is the primary key and
the other the date is part of a compound key because it isn’t unique.
It’s like one table as an index for another.

----------------------------------------------------------------------
rvk · 2024-01-29 15:59

Quote from: Joanna on January 29, 2024, 04:18:36 pm@rvk I think two
separate tables are needed to represent what happened on a particular
date. So in one table the date is the primary key and the other the
date is part of a compound key because it isn’t unique. It’s like one
table as an index for another.
Yes, but one table with just only a date isn't useful then. You can
just as well merge the datefield to the original table and use that
for the compound key. If you do it for another field with VARCHAR then
it becomes more useful.

Then you would have a (compound) index with an element pointing to
another index. That only makes sense if there is more data then only
one date.

----------------------------------------------------------------------
Nicole · 2024-01-29 16:20

Thank you!
I tend to take over the date as new date-field.

Thrilling is this notice:
"...varchart....".

Indeed, this is my second topic. I HAVE a lot of varcharts which I
address the same way - as foreign keys.
In former times, the varchart were something I just typed in.
Now they are UTF8.

So - in German we say - A Fool asks more than a wise man can answer -
my next question is this:
It is clear, that a VarChart of 15 signs is more than a foreign key.

But - What about my tentousands of varchar(2)?
They are UTF8 now.
If I do not err, these are 2 Byte? The same as ASCII?
Or more?

I used this table:
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref4
0/fblangref40-datatypes.html

The table for these characters is that limited, that a shortint
primary key would be more than enough.
Would it be better to use the VarChar(2) or s shortInt foreign key?
Or would you say, this does not matter?

----------------------------------------------------------------------
rvk · 2024-01-29 17:24

Quote from: Nicole on January 29, 2024, 05:20:11 pmWould it be better
to use the VarChar(2) or s shortInt foreign key?
Or would you say, this does not matter?
The problem with CHAR(2) is that it works different from INTEGER.
An integer can just be compared.
With a char(2) you first need to compare the first character. If they
are the same you need to check the second. etc. That's inherently less
efficient for an index (not to speak of the combo with another field
you had because then it definitely less efficient).

But you could get away with it with only a char(2) as PK (but that
also depends on the rest of the DB design). As said, I usually have
one generated ID per table with which I connect everything together.

----------------------------------------------------------------------
Joanna · 2024-01-29 23:02

@rvk
Indeed it would make no sense to have table with just a date column.
In my case the table with unique dates has user editable string
columns and is used to look up more information from the larger table
with non unique dates.

----------------------------------------------------------------------
egsuh · 2024-01-30 06:48

QuoteMy question was,
Is it better to save the date as a field or as a foreign key?
I'm really confused. To define a foreign key constraint, shouldn't
there a field for that foreign key, i.e. Table2 here? AFAIK foreign
keys are defining the relationships between the fields of different
tables so that the data integrity is maintained.

If I'm right, the quoted question is weird. The date should be saved
as a field. You have option to define foreign key or not, not whether
to save as a field or as a foreign key.

If a foreign key is defined between Table1.MyDate and Table2.MyDate,
Doesn't it mean something like the Table2.MyDate must be one of
Table1.MyDate, or vice versa?

----------------------------------------------------------------------
Zvoni · 2024-01-30 07:47

Quote from: egsuh on January 30, 2024, 07:48:37 amQuoteMy question
was,
Is it better to save the date as a field or as a foreign key?
I'm really confused. To define a foreign key constraint, shouldn't
there a field for that foreign key, i.e. Table2 here? AFAIK foreign
keys are defining the relationships between the fields of different
tables so that the data integrity is maintained. No. You can declare a
Foreign key pointing to the Primary Key of the same table.
Again no to the second part. Referential integrity is only maintained,
if you declare it as such (ON UPDATE/DELETE CASCADE/RESTRICT)
The only thing a Foreign Key (with no other rules defined!!) is
actually enforcing is, if you do actually pass a Value to that FK-
Field, the parent-record must exist (might be in another table or even
in the same table. see above)

QuoteIf I'm right, the quoted question is weird. The date should be
saved as a field. You have option to define foreign key or not, not
whether to save as a field or as a foreign key.

If a foreign key is defined between Table1.MyDate and Table2.MyDate,
Doesn't it mean something like the Table2.MyDate must be one of
Table1.MyDate, or vice versa?
Again no.
You can define Foreign Keys allowed to be NULL (so called "orphaned"
child-records)

----------------------------------------------------------------------
Zvoni · 2024-01-30 08:10

After rereading nicole's other threads i'm more and more convinced,
that this is actually a "m:m"-scenario, if she wants to keep the
"calendar"-table, or if she doesn't want to keep the "calendar"-table
that the relation is the other way round

----------------------------------------------------------------------
egsuh · 2024-01-30 09:19

QuoteNo. You can declare a Foreign key pointing to the Primary Key of
the same table.

Again no to the second part. Referential integrity is only maintained,
if you declare it as such (ON UPDATE/DELETE CASCADE/RESTRICT)11
The only thing a Foreign Key (with no other rules defined!!) is
actually enforcing is, if you do actually pass a Value to that FK-
Field, the parent-record must exist (might be in another table or even
in the same table. see above)

You can define Foreign Keys allowed to be NULL (so called "orphaned"
child-records)
Well, these read to me:

There should be a field in the table, whether it is directly defined
in a foreign key or via pointer to primary key. I don't see any
practical difference.

Referential integrity don't have to mean full integrity. I know NULLs
are allowed in Foreign keys. Anyway the most basic funciton of foreign
key is to make sure that parent-record exists, if any value is defined
in the FK-keys of the child record. And I may add more integrity
checks.

Is this right?


Besides that, is there any usage of foreign keys, like in joining
tables? Normally it would look like

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
p.ProductName, pu.Purchased, s.SoldFROM Products p INNER JOIN Purchase
pu ON p.ProductID = pu.ProductIDINNER JOIN Sale s ON s.ProductID =
p.ProductID
Can I use foreign keys instead of like on p.ProductID = pu.ProductID ?

----------------------------------------------------------------------
alpine · 2024-01-30 10:02

Foreign keys are mechanism to enforce DRI. As simple as that.

Quote from: Zvoni on January 30, 2024, 09:10:59 amAfter rereading
nicole's other threads i'm more and more convinced, that this is
actually a "m:m"-scenario, if she wants to keep the "calendar"-table,
or if she doesn't want to keep the "calendar"-table that the relation
is the other way round
I'm still puzzled why one will design a calendar table to be a master
... the only thing that comes to me is if she wants every particular
day to be described in advance (e.g. as workday/holiday or something).

----------------------------------------------------------------------
Zvoni · 2024-01-30 10:17

Quote from: alpine on January 30, 2024, 11:02:31 amForeign keys are
mechanism to enforce DRI. As simple as that.

Quote from: Zvoni on January 30, 2024, 09:10:59 amAfter rereading
nicole's other threads i'm more and more convinced, that this is
actually a "m:m"-scenario, if she wants to keep the "calendar"-table,
or if she doesn't want to keep the "calendar"-table that the relation
is the other way round
I'm still puzzled why one will design a calendar table to be a master
... the only thing that comes to me is if she wants every particular
day to be described in advance (e.g. as workday/holiday or something).
Alpine, there are scenarios, where you need a "calendar"-table.
Think of "Schedules" or "Holiday-Planning in a Company" and similiar".

It does help to know beforehand if a Date is a Sunday, or a Holiday
which falls to mid-week etc.
especially if you have to present it visually.
Think Excel-Sheet with the first column being consecutive Dates, the
other columns employees (present/absent etc.)

But i agree: It's not making much sense to USE such dates from a
Calendar-table as Primary Key to a Foreign Key in another table.
For me it would just be premade table of values i can connect to in
JOINs, and for JOINs it doesn't have to be PK/FK-Combinations

----------------------------------------------------------------------
alpine · 2024-01-30 10:41

Quote from: Zvoni on January 30, 2024, 11:17:13 am<snip>
But i agree: It's not making much sense to USE such dates from a
Calendar-table as Primary Key to a Foreign Key in another table.
For me it would just be premade table of values i can connect to in
JOINs, and for JOINs it doesn't have to be PK/FK-Combinations
I happen to be the principal author of a T&A program. I have a table
for describing calendar days too, but it helps in describing
exceptions, e.g. national holidays and such. Actually I'm caching it
entirely into the RAM in time when I'm making intensive calculations.
But it never came to my mind to use it like a "master".

----------------------------------------------------------------------
Zvoni · 2024-01-30 11:11

Quote from: alpine on January 30, 2024, 11:41:57 amI happen to be the
principal author of a T&A program. I have a table for describing
calendar days too, but it helps in describing exceptions, e.g.
national holidays and such. Actually I'm caching it entirely into the
RAM in time when I'm making intensive calculations. But it never came
to my mind to use it like a "master".
Me neither, especially since a "calendar"-table in the way TS is using
it, it becomes "hardcoded" a.k.a. you have to "expand" (read: INSERT)
new dates by hand

If i need a "calendar" i usually use a dynamic CTE, where i can define
start and end-date on the fly.
That way i can even use it in Queries where i need a "flexible" time-
window

----------------------------------------------------------------------
alpine · 2024-01-30 11:48

Quote from: Zvoni on January 30, 2024, 12:11:53 pmMe neither,
especially since a "calendar"-table in the way TS is using it, it
becomes "hardcoded" a.k.a. you have to "expand" (read: INSERT) new
dates by hand
More than once there was an appeal to her to give the code to the
database to be cracked in a minute, but it seems that it is
classified 

Now I bet, a question about CTE will follow in a separate thread...

----------------------------------------------------------------------
Zvoni · 2024-01-30 11:51

Quote from: alpine on January 30, 2024, 12:48:00 pmMore than once
there was an appeal to her to give the code to the database to be
cracked in a minute, but it seems that it is classified 

Now I bet, a question about CTE will follow in a separate thread...
Sucker bet....

----------------------------------------------------------------------
Joanna · 2024-01-30 12:52

Is it unusual for one table to be used to reference alothrr table
though?
Like in my project I have a table in dbgrid that serves sort of as a
table of contents for viewing what is in larger table.
For instance something to look up the history of multiple events which
happened on certain date..

----------------------------------------------------------------------
Nicole · 2024-01-30 13:47

In the meanwhile I skipped the whole table tbcalendar and removed the
foreign key-construction.

Yes, this is correct: The tbcalendar's purpose was to get information
in advance, e.g. Sunday or holiday, Daylight-Saving and more.
And yes, the consequences of this construction were, that I cannot
insert data, of which the tbcalendar did not hold the date as pk.
The tbcalendar did hold neither Saturday nor Sunday and case something
tries to grab one of them, this resulted in a wanted(!) error.

My problem with the tbcalendar was in the long run, that the varchar
fields reserved became too short sometimes.
And ONE information of a kind a day was sometimes not enough. As I
created the database, I was not aware of this.

So my new construction is more flexible in the tables, but has less
control.
I had to write checks, if Saturday or Sunday are "hit". Cross fingers,
I do not forget one.

Thank you all for your comments.

----------------------------------------------------------------------
Zvoni · 2024-01-30 14:21

Quote from: Joanna on January 30, 2024, 01:52:25 pmIs it unusual for
one table to be used to reference alothrr table though?
Like in my project I have a table in dbgrid that serves sort of as a
table of contents for viewing what is in larger table.
For instance something to look up the history of multiple events which
happened on certain date..
Not unsual at all. On the contrary: It's the main principle behind
"Normalization" --> "Don't store Data twice (or more times)"

Imagine a Database having the Addresses of all Citizens of China, and
you want to store the Adresses of Citizens of Shanghai, China
(Shanghai has some 25 Million Citizens)
If you would have only one table with Fields like (very simplified!!)
First Name, Last Name, Street, ZIP-Code, City

What's the Consequence?
Exactly, you would have 25 Million times the word/text/string
"Shanghai" in that table
Since in this era Unicode is common it would mean, that each Entry of
"Shanghai" would take up 16 Bytes (or more if you use UTF32)
25,000,000 x 16 Bytes = 400,000,000 Bytes (400MB) (DON'T START ON
CONVERSION OF BYTE TO MB!! THIS IS JUST TO ILLUSTRATE)
Result: You "wasted" 399,999,984 Bytes

In a relational model you would have a Table "City" which has only one
single Record of "Shanghai" with an additional Field "ID" which would
be an Auto_ID Integer Primary Key.
That additional Field "ID" takes up 4 (or 8 ) Bytes, depending which
Integer-Type you use.
So for this entry, we need 20 Bytes (4 Bytes for the ID, 16 Bytes for
"Shanghai")
In a second table you would actually have the Addresses of the People
with Fields like
First Name, Last Name, Address, ZIP-Code, ForeignKeyToCity_ID
And in that last Field "ForeignKeyToCity_ID" you store the Value of
the ID you get from "City", which is again only 4 (or 8 ) Bytes
Result: a "save" of 300,000,000 bytes (4 Bytes instead of 16)

Yes, i'm aware that this can be broken down even further (ZIP-Code in
its own table), but as i said: This is to illustrate, WHY you (want)
to have/use Keys referencing other Tables

----------------------------------------------------------------------
Joanna · 2024-01-30 14:56

If you have table for every city that would be a lot of cities and
what if someone moves to another city? How about a table for zip codes
and their cities then only use zip codes instead of city name for
addresses?

----------------------------------------------------------------------
rvk · 2024-01-30 15:07

Quote from: Joanna on January 30, 2024, 03:56:07 pmIf you have table
for every city that would be a lot of cities and what if someone moves
to another city?No no, you don't have a table for every city. You have
a tables WITH ALL the cities.

Not normalized:

CLIENT:
ID bigint
NAME varchar(50)
STREET varchar(50)
POSTALCODE varchar(50)
CITY varchar(50)
COUNTRY varchar(50)

The CITY and COUNTRY takes up much space... especially if most of your
clients live in the same city and country it's better to normalize.
https://en.wikipedia.org/wiki/Database_normalization

CLIENT:
ID bigint
STREET varchar(50)
POSTALCODE varchar(50)
CITY_ID bigint foreign key to city
COUNTRY bigint foreign key to country

CITY:
ID bigint
NAME varchar(50)

COUNTRY:
ID bigint
NAME varchar(50)

Here the each city name only take up 1x varchar(50) PER city + per
client a pointer to that city per client. But's that way less then
repeating every city per client.

Now when the clients moves to another city you can just lookup the
city in the table. If it doesn't exist you can add it and use the
foreign key to point to its ID.

That's how normalization works (which is a normal stage of database
design).

Now... you can go too far with this and sometimes it defeats its
purpose.

I myself, DO have the city name in my table for client (because it
changes a lot per client). BUT I have a separate table per country.
This is because I need a table for country anyway to put the specific
VAT percentages, country export code, etc. per country. And because I
already have that table I can just as well use it.

----------------------------------------------------------------------
Zvoni · 2024-01-30 15:30

Quote from: Joanna on January 30, 2024, 03:56:07 pmIf you have table
for every city that would be a lot of cities and what if someone moves
to another city? How about a table for zip codes and their cities then
only use zip codes instead of city name for addresses?
Joanna, my example was just to ILLUSTRATE.
As rvk pointed out: This could be broken down even further, nevermind
running into "m:m"-scenarios,
but you will reach a point, Normalization defeats itself, specifically
if a ForeignKey takes up the same amount of space as the actual Data
you want to reference.
That's why we were all so puzzled why someone would use a Date-Field 
(which is basically just an Integer!) in one table as a reference-
Field for a ForeignKey in another table, because "space"-wise you gain
nothing

----------------------------------------------------------------------
alpine · 2024-01-30 16:10

Quote from: Joanna on January 30, 2024, 01:52:25 pmIs it unusual for
one table to be used to reference alothrr table though?
Оn the contrary, it is the normal way.

Quote from: Joanna on January 30, 2024, 01:52:25 pmLike in my project
I have a table in dbgrid that serves sort of as a table of contents
for viewing what is in larger table.
For instance something to look up the history of multiple events which
happened on certain date..
Splitting tables in a master-detail ones is actually a result of
database Normalization and that is the process of structuring the
database in a way that prevents insertion, update and deletion
anomalies, see the above link. That is the primary aim of it, not the
size reduction. The reduction comes as a result of normalization. Most
of the experienced database designers even make that normalization
intuitively.

Your example with the larger-smaller (consolidated) table is not an
example of normalized relation, since the data into the smaller table
is actually contained into the larger one. Redundancy isn't allowed.

Sometimes the opposite process exists, de-normalization, but that is
even more advanced topic as it looks like you're not yet familiar with
the former one.

----------------------------------------------------------------------
Nicole · 2024-01-30 18:52

The migration is complete. The database is quick like hell.
Space? - about 60 percent larger than before.

----------------------------------------------------------------------
Zvoni · 2024-01-30 18:59

Quote from: Nicole on January 30, 2024, 07:52:00 pmThe migration is
complete. The database is quick like hell.
Space? - about 60 percent larger than before.
Then there is still something wrong with your design, but at least
good news on performance

----------------------------------------------------------------------
rvk · 2024-01-30 19:57

Quote from: Zvoni on January 30, 2024, 07:59:50 pmQuote from: Nicole
on January 30, 2024, 07:52:00 pmThe migration is complete. The
database is quick like hell.
Space? - about 60 percent larger than before.
Then there is still something wrong with your design, but at least
good news on performance
That does indeed sound strange but we've never seen anything about the
database. So as long as this stays the same, we can't say anything for
certain.

Maybe the old database didn't have any indexes etc.
But there can also be something terribly wrong with the new DB.
(Huge default page, inefficient table design etc.)

Anyway... With gstat you can get a lot of statistics about the DB.
https://firebirdsql.org/file/documentation/html/en/firebirddocs/gstat/
firebird-gstat.html

----------------------------------------------------------------------
Joanna · 2024-01-30 22:41

Maybe her new database is very well indexed and the indices take a lot
of space ?
 I’ve never thought about converting redundant strings into one string
with numerical code before.

About using the date field in two tables , it could have different
purposes in the different tables. In my case selecting the date in one
dbgrid viewing the table1 will display a dataset in a different dbgrid
from the Much larger table with everything related to the date in
smaller table.

Just because both contain dates does not mean that the dates should be
combined into one place because there is different information
attached to the dates in table1.

There is something that concerns me though. I’m storing the date time
as a float. Does that take up too much space?

----------------------------------------------------------------------
rvk · 2024-01-30 22:53

Quote from: Joanna on January 30, 2024, 11:41:57 pmJust because both
contain dates does not mean that the dates should be combined into one
place because there is different information attached to the dates in
table1.
Exactly. And that last piece of information (having different
information attached to that date field) was not present in the
original question. Just a table with a single date, nothing else. In
your case (date with the extra info) it's even recommended to create
its own table.

But with both dates in their own table it might not even be necessary
to link them together with a foreign key. They are both their own
identity (dates) and you can still use them when JOINing them together
(you might want to add an index on them in that case but for that a
constraint/fk isn't strictly needed). Unless you need to check that
the date exists in both tables, then you can add the constraint (i.e.
foreign key).

----------------------------------------------------------------------
rvk · 2024-01-30 22:58

Quote from: Joanna on January 30, 2024, 11:41:57 pmThere is something
that concerns me though. I’m storing the date time as a float. Does
that take up too much space?
In Firebird?

DATE is 32 bits. FLOAT is also 32 bits.
https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref4
0/fblangref40-datatypes.html

But why did you use float?

Edit: O wait. Date time. Did you mean TIMESTAMP?

----------------------------------------------------------------------
Joanna · 2024-01-30 23:05

I use sqlite. It says that tdatetime is also a float so I decided it
would be easier to store as float. I’m storing dates for both noon and
midnight so two different timestamps per date.

----------------------------------------------------------------------
rvk · 2024-01-30 23:21

Quote from: Joanna on January 31, 2024, 12:05:45 amI use sqlite. It
says that tdatetime is also a float so I decided it would be easier to
store as float. I’m storing dates for both noon and midnight so two
different timestamps per date.
That's a whole different story.
SQLite has its own datatypes and translates those to a few types.
https://www.sqlite.org/datatype3.html

SQLite is a bit of an odd duck regarding datatypes.

DATE can be stored as different types.
The fields there are actually typeless. So you can store anything in
every field. It depends on the way you execute the insert. It's
completely different from standard SQL in that way.

https://www.sqlite.org/datatypes.html

It's useful for a lot of things but sometimes you do need to remind
yourself about some of its quirks
https://www.sqlite.org/quirks.html

BTW it depends how you do the date to float. Are you converting it in
code and storing it? Float is not an extract datatype so I wouldn't
trust it with a datetime for milliseconds. Days and time to servings
should be fine I think.

----------------------------------------------------------------------
Joanna · 2024-01-30 23:48

To represent noon, tdatetime puts a .5 at end . I’m not sure how other
times are represented. I’m not doing anything precise with
milliseconds or anything like that

----------------------------------------------------------------------
Zvoni · 2024-01-31 07:27

Quote from: Joanna on January 31, 2024, 12:48:54 amTo represent noon,
tdatetime puts a .5 at end . I’m not sure how other times are
represented. I’m not doing anything precise with milliseconds or
anything like that

Other times are represented like this:
xxx.75 --> 6 PM / 18:00h ("Three quarters of the Day")
xxx.25 --> 6 AM / 06:00h ("One quarter of the Day")
and so on....

Then use TEXT DataType in SQLite, and store everything in ISO-Format
as a String and be done with it. SQLite understands datetime-strings
natively.
Upside: If for whatever reason you have to dive into the
Database/Table directly with a Management-Program (e.g. DB Browser for
SQLite) you get "readable" raw dates.

Fetching those dates is easy enough with
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
DATE(MyDateTimeAsText) AS MyDateTime FROM MyTable WHERE ..... and an
Entry for MyDateTimeAsText looks like
'2024-01-31 08:27:25'

and even sorting by the raw DateTime-String works
...ORDER BY MyDateTimeAsText

I would be careful with floating-point Datatypes for DateTimes.
as rvk pointed out, Float is 4 Bytes, a TDateTime is actually an alias
for Double, which is 8 Bytes

That said: I'm aware, that a DateTime as String takes up more space as
a simple Float (or REAL how it's called in SQLite), but you have to
weigh the Pros and Cons of it

----------------------------------------------------------------------
rvk · 2024-01-31 10:15

Quote from: Zvoni on January 31, 2024, 08:27:27 amThen use TEXT
DataType in SQLite, and store everything in ISO-Format as a String and
be done with it. SQLite understands datetime-strings natively.
You don't even need to use TEXT. You can just use DATE.

When you use FLOAT or REAL or whatever you can even still just push in
TEXT into that field.
QuoteSQLite is "typeless". This means that you can store any kind of
data you want in any column of any table, regardless of the declared
datatype of that column.https://www.sqlite.org/datatypes.html

That's what makes SQLite a real odd duck

----------------------------------------------------------------------
Joanna · 2024-01-31 10:55

You are right, tdatetime is a double and that’s how it’s stored. It’s
true that viewing the database outside the program is rather
confusing...

I don’t have any plans to convert it to strings however, I like
keeping it as a tdatetime and using tdatetime functions. I format the
dates unto something readable before displaying them to users of
course..

To be honest I don’t know very much about sql, just enough to get by.
I get too distracted with the pascal related stuff..
I discovered a few years back that a program that worked great with a
small test database choked on a real database. So I had to start all
over again..

----------------------------------------------------------------------
Zvoni · 2024-01-31 10:55

Quote from: rvk on January 31, 2024, 11:15:05 amYou don't even need to
use TEXT. You can just use DATE.There is no DATE-type in SQLite. Only
storage-classes for Date/Times (TEXT, REAL and Integer), and its
Affinities SQLite TRANSLATES to a Storage-Class during CREATION of a
Table.
And i've seen enough threads where people complained getting Garbage,
just because they think "DateTime" is a valid Datatype in SQLite.
You should read about how SQLite PARSES the CREATE TABLE-Statement,
and then assigns Storage-Classes to the Datatypes found.

Even a Typo like "  MyDateColumn DATETME" would get a Storage-class
because the Parser found "DATE" in it

QuoteWhen you use FLOAT or REAL or whatever you can even still just
push in TEXT into that field.
QuoteSQLite is "typeless". This means that you can store any kind of
data you want in any column of any table, regardless of the declared
datatype of that column.https://www.sqlite.org/datatypes.html

That's what makes SQLite a real odd duck 
I know that.
Still think it's better practice to use the "native"
DataTypes/Storage-Classes, than whatever SQLite thinks is appropriate.
It's the Users/Developers Obligation to make sure, that any Data gets
stored in the correct column

In that Sense, SQLite is very comparable to an Excel-Spreadsheet:
I can define the Format for a Column to be numeric, but noone prevents
me to enter any text there

----------------------------------------------------------------------
Nicole · 2024-01-31 20:05

No, the DB should not be that huge.
I was really surprised as I saw this.

................... wait. I checked something:
This codepage thing: It was 4096 and now it is 8192 ?!
I never was asked for this and trusted to be 2048 or so as default.

About this codepage thing.
My ideas are very vage about it.
What is "good"?
IF the database is quicker by this, the huger sizes would be ok to me.
But to be honest, I am not sure, what codepages is good for.
Is this something like those sectors in the FAT and NTFIS?
If yes, how to chose the best size?

And how can I change it?
Must I rewrite the whole database again or can I do it by backup and
play back?

----------------------------------------------------------------------
rvk · 2024-01-31 20:14

Quote from: Nicole on January 31, 2024, 09:05:18 pmIs this something
like those sectors in the FAT and NTFIS?Yes, it's something like that.
https://www.ibexpert.net/ibe/pmwiki.php?n=Doc.PageSize
https://ibdeveloper.blogspot.com/2008/09/what-page-size-i-should-use-
in-my.html?m=1
Also see point 15 here
https://ib-aid.com/en/articles/23-more-ways-to-speed-up-firebird/

Default increased to 8192 in FB 3.

Quote from: Nicole on January 31, 2024, 09:05:18 pmIf yes, how to
chose the best size?
It depends on how many space is wasted and how large the DB get
(larger DBs are better with larger pagesizes).

Quote from: Nicole on January 31, 2024, 09:05:18 pmAnd how can I  it?
Must I rewrite the whole database again or can I do it by backup and
play back?
You can change it by backing up the database and restoring it with a
specified pagesize.

But if you are certain that is the cause of the large FB (you can
check with gstat or backup and look at the backup file) and the larger
DB is not a problem, you can keep it at that size.

----------------------------------------------------------------------
alpine · 2024-01-31 20:39

Wasn't it the new encoding UTF-8 that makes the difference?

----------------------------------------------------------------------
rvk · 2024-01-31 20:56

Quote from: alpine on January 31, 2024, 09:39:15 pmWasn't it the new
encoding UTF-8 that makes the difference?
UTF-8 shouldn't make any difference. Most normal characters take just
1 byte (just as the ISO codings). UTF-16 on the other hand would have
made a difference (minimum 2 bytes per character).

----------------------------------------------------------------------
alpine · 2024-02-01 04:47

Quote from: rvk on January 31, 2024, 09:56:58 pmUTF-8 shouldn't make
any difference. Most normal characters take just 1 byte (just as the
ISO codings).
Yours "normal". But how many bytes Firebird allocates for a CHAR
fields? I'm not a Firebird expert, but I suspect char fields will be
quadrupled in allocation size.

----------------------------------------------------------------------
rvk · 2024-02-01 07:28

Quote from: alpine on February 01, 2024, 05:47:51 amQuote from: rvk on
January 31, 2024, 09:56:58 pmUTF-8 shouldn't make any difference. Most
normal characters take just 1 byte (just as the ISO codings).
Yours "normal". But how many bytes Firebird allocates for a CHAR
fields? I'm not a Firebird expert, but I suspect char fields will be
quadrupled in allocation size.
For the CHAR fields you are correct. But I never use CHAR for strings.
I use VARCHAR. It has a length part (2 bytes) and is only as long as
the string in bytes (+2).

But it might be another thing with indexes on strings. Those can
quadruple.
That on combination with that increased page will definitely make an
impact.
So you shouldn't throw on indexes where they are not needed.
And prefer to use the generated ID above a string for foreign and
primary key 

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref3
0/fblangref30-datatypes-chartypes.html

----------------------------------------------------------------------
alpine · 2024-02-02 08:54

Quote from: rvk on February 01, 2024, 08:28:44 amFor the CHAR fields
you are correct. But I never use CHAR for strings. I use VARCHAR. It
has a length part (2 bytes) and is only as long as the string in bytes
(+2).

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref3
0/fblangref30-datatypes-chartypes.html
How this makes difference at the file storage level (CHAR vs VARCHAR)?
Explain please.
AFAIK they differ in returned values to the client, CHAR being padded
to its field length and VARCHAR being not. But on a storage level they
must accommodate the biggest possible length , which is max number of
chars x4 for UTF-8. Is it somehow related to the MVCC?

----------------------------------------------------------------------
Nicole · 2024-02-02 09:25

Yes, I increased the number of digits of my character in some of my
fields, as VarChar, not as Char.
The main idea was, that rare cases of longer entries should work as
well.

Most of my fields are defined as VarChar(2).

This UTF8 - I thought, this 8 stands for 8 bits = 1 byte?
I thought, my VarChar(2) would take 2 bytes.
Is this correct?

----------------------------------------------------------------------
rvk · 2024-02-02 09:40

Quote from: alpine on February 02, 2024, 09:54:41 amHow this makes
difference at the file storage level (CHAR vs VARCHAR)? Explain
please.
AFAIK they differ in returned values to the client, CHAR being padded
to its field length and VARCHAR being not. But on a storage level they
must accommodate the biggest possible length , which is max number of
chars x4 for UTF-8. Is it somehow related to the MVCC?No, the VARCHAR
doesn't need to reserve all the space. It just saves the record with
the length of the string +2 characters for length.
It's in the link I gave.

QuoteVARCHAR is the basic string type for storing texts of variable
length, up to a maximum of 32,765 bytes. The stored structure is equal
to the actual size of the data plus 2 bytes where the length of the
data is recorded.https://firebirdsql.org/file/documentation/chunk/en/r
efdocs/fblangref30/fblangref30-datatypes-chartypes.html

Quote from: Nicole on February 02, 2024, 10:25:28 amYes, I increased
the number of digits of my character in some of my fields, as VarChar,
not as Char.
The main idea was, that rare cases of longer entries should work as
well.

Most of my fields are defined as VarChar(2).
I'm not sure if that's very efficient. I would define it as CHAR(2).
VARCHAR(2) is 2 bytes for length and 8 bytes for the characters UTF-8.
While CHAR(2) is just only the 8 bytes.
(BTW. I wouldn't even store that, what was it? region digit?, in a
CHAR or VARCHAR, I would make a separate table for it. If it's a
region, branche or something, you may want to add something else
specific about that entity. And that's where the separate table comes
in. Just linked with a BIGINT.

For CHAR(x) you do need to take into account that the string is padded
with spaces to the length of 2.

Quote from: Nicole on February 02, 2024, 10:25:28 amThis UTF8 - I
thought, this 8 stands for 8 bits = 1 byte?
I thought, my VarChar(2) would take 2 bytes. Is this correct?Yes and
No. UTF-8 does stand for 8 bits encoding but characters can be encoded
to multiple bytes.
So while a to z just take 1 byte, other characters (for example
Chinese or some accent characters) are encoded in multiple characters.
See an example here: https://en.wikipedia.org/wiki/UTF-8
So £ is saved as $C2 + $A3

Quotecode points in the ASCII range (0-127) are represented by a
single byte
code points in the range (128-2047) are represented by two bytes
code points in the range (2048-65535) are represented by three bytes
and code points in the range (65536-1114111) are represented by four
bytes.
(This may seem like a lot of possible characters, but keep in mind
that in Chinese alone, there are 100,000s of
characters.)https://www.freecodecamp.org/news/what-is-utf-8-character-
encoding

So UTF-8 will be maximum 4 bytes long and Firebird needs to reserve
those bytes for a (per) CHAR.

And just to make it more confusing... it's possible to reach even more
characters by combining the code points.
QuoteA "character" can take more than 4 bytes because it is made of
more than one code point. For instance a national flag character takes
8 bytes since it is "constructed from a pair of Unicode scalar values"
both from outside the BMP.So if you save 1 national flag character,
both code points of your CHAR(2) will be occupied and you can't add
anything more (you will get a string too long error when saving).

----------------------------------------------------------------------
Nicole · 2024-02-02 10:49

I have only a handful of different VarChar(2).

My problem was, that there may be sometimes only one digit if using
Char(2)
Then there was e.g. " W" oder "W " instead of "W".
This may seem easy to you and you may think "just use trim()".
However it was a huge problem and not that easy. You do not want to
know details.

I thought to solve this by VarChar. Then there will not be one blank
inside troubling me.

An alternative would be SmallInt als fk:
Then I give my VarChars into their own table and set a foreign key to
their primary key.
And yes, each VarChar has some more fields.
The advantage of saving them in an own table was more tables were
"stand alone".
But it seemed, that I erred in really many points about the storage
(thank you so much for taking time to explain)

My structure is like this:
sum of these varchars: about 30 or 40 all in all
digits: 1 or 2
when they are needed as field  / possible fk: hundred thousands of
times.

Would you recommend, that I put them into a small table and give it a
smallInt as primary key?
Then I can change those 100.000s of fields to a foreign key.

Will it be the same, if I do not give those small table a 2 digit-
ASCII as primary key?
Or will this increase the storage of the mass of foreign keys?
The advantage of the ASCII would be, that I know on looking at them,
what they mean without checking a figure for it.

----------------------------------------------------------------------
rvk · 2024-02-02 11:12

Quote from: Nicole on February 02, 2024, 11:49:18 amThen there was
e.g. " W" oder "W " instead of "W".What does W stand for ???

Quote from: Nicole on February 02, 2024, 11:49:18 amWould you
recommend, that I put them into a small table and give it a smallInt
as primary key?
Then I can change those 100.000s of fields to a foreign key.It depends
on what "W" exactly is. If it's something like F for false and T for
true then yes, by all means leave them there.

If it means W for World, D for Germany, NL for Netherlands, then
putting them in their own table could be advantagues in the long run
because you can add more description or other fields to the table with
W, D and NL (which you can't if they are in that other table). And if
"W" is also a foreign key where you ALSO store the "W" then it's
better to use ID. Because if you don't you would need to work with ON
UPDATE CASCADE for when the code changes. That's why it's sometimes
better to use ID as PK. But we still haven't seen much of your DB
(DDL) design so it's hard to tell.

Quote from: Nicole on February 02, 2024, 11:49:18 amThe advantage of
the ASCII would be, that I know on looking at them, what they mean
without checking a figure for it.
That's where the INNER JOINs come in (note: inner not left join).
If you know the "W" then you would always do
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT A.*
FROM TABLE1 AINNER JOIN TABLE2 A ON A.ID=B.ID_TB2WHERE B.NAME = 'W'or
even
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT A.*
FROM TABLE1 AINNER JOIN TABLE2 A ON A.ID=B.ID_TB2 AND B.NAME = 'W'
But again... it depends on the rest of your DDL (which we know nothing
about).

----------------------------------------------------------------------
Nicole · 2024-02-02 11:42

LOL, your fantasy of my work is far too huge. There is not much
special about it.
You know it all in the meanwhile.

W stands for Wheat.

And there is really not much more mystery in it.
The Wheat price is - I think so - taken from the harbor in
Minneapolis.
And contains the price back for a really long period of time.

The table says a little bit more than:
the new pk   ***    'W'   ***   date as TDate   ***   price a bushel
as TCurrency

No more children, no more complexity.
And yes, there is more information about W.
But none, which needs joins.


The table which holds the wheat looks e.g.  like this

pk as TSmallInt   ***    "W"  ***   info1  ***   figure 1   *** 
 aTime1 as TTime *.... and not too much more

----------------------------------------------------------------------
rvk · 2024-02-02 11:49

Quote from: Nicole on February 02, 2024, 12:42:57 pmNo more children,
no more complexity.
And yes, there is more information about W.
But none, which needs joins.
If you don't need the name "Wheat" somewhere in your reports and you
don't have other tables pointing to "W" field somehow, then it's an
entity on its own and you can leave it as VARCHAR(2).

----------------------------------------------------------------------
alpine · 2024-02-02 12:40

Quote from: rvk on February 02, 2024, 10:40:10 amQuote from: alpine on
February 02, 2024, 09:54:41 amHow this makes difference at the file
storage level (CHAR vs VARCHAR)? Explain please.
AFAIK they differ in returned values to the client, CHAR being padded
to its field length and VARCHAR being not. But on a storage level they
must accommodate the biggest possible length , which is max number of
chars x4 for UTF-8. Is it somehow related to the MVCC?No, the VARCHAR
doesn't need to reserve all the space. It just saves the record with
the length of the string +2 characters for length.
It's in the link I gave.

QuoteVARCHAR is the basic string type for storing texts of variable
length, up to a maximum of 32,765 bytes. The stored structure is equal
to the actual size of the data plus 2 bytes where the length of the
data is recorded.https://firebirdsql.org/file/documentation/chunk/en/r
efdocs/fblangref30/fblangref30-datatypes-chartypes.html
I have read this. Also I've read https://www.firebirdsql.org/file/docu
mentation/html/en/firebirddocs/firebirdinternals/firebird-
internals.html

While it is not very exhaustive, I can imagine that for each new
version of the record, VARCHARs were written with their actual length,
i.e. the row is 'packed' and it is only with the significant data with
it. But why this doesn't apply for the CHARs too? The tail padding can
be skipped also, thus way making them again 'packed'. But:
QuoteKeep in mind that one UTF8 character occupies up to 4 bytes, thus
limiting the size of CHAR fields to 8,191 characters (32,767/4).That
is in para 3.5.1 of your link.

----------------------------------------------------------------------
rvk · 2024-02-02 13:01

Quote from: alpine on February 02, 2024, 01:40:32 pmQuoteKeep in mind
that one UTF8 character occupies up to 4 bytes, thus limiting the size
of CHAR fields to 8,191 characters (32,767/4).That is in para 3.5.1 of
your link.
Yes. That means you can't define a CHAR(8192) or larger when your
database character set is UTF-8.
( if you try to create something larger you get a "implementation
limit exceeded" )
Note... if your character set is NONE or some ISO then you can go up
to 32767 and although you can store UTF-8 in NONE fields, you'll get
an error if the size exceeds the 8191.

Quote from: alpine on February 02, 2024, 01:40:32 pmI have read this.
Also I've read https://www.firebirdsql.org/file/documentation/html/en/
firebirddocs/firebirdinternals/firebird-internals.html

While it is not very exhaustive, I can imagine that for each new
version of the record, VARCHARs were written with their actual length,
i.e. the row is 'packed' and it is only with the significant data with
it.A nice snippet is here for showing the size of all fields:
(source https://stackoverflow.com/a/69415593/1037511)

Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
T.RDB$RELATION_NAME, RF.RDB$FIELD_NAME, RF.RDB$NULL_FLAG,
F.RDB$FIELD_TYPE,     F.RDB$FIELD_LENGTH,    F.RDB$CHARACTER_LENGTH, 
 F.RDB$FIELD_LENGTH / RCS.RDB$BYTES_PER_CHARACTER AS calculatedFROM
RDB$RELATIONS TINNER JOIN RDB$RELATION_FIELDS RF  ON
RF.RDB$RELATION_NAME = T.RDB$RELATION_NAMEINNER JOIN RDB$FIELDS F  ON
F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCELEFT JOIN RDB$CHARACTER_SETS
RCS  ON RCS.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID WHERE
T.RDB$VIEW_BLR IS NULL AND (T.RDB$SYSTEM_FLAG IS NULL OR
T.RDB$SYSTEM_FLAG = 0)  AND (RF.RDB$SYSTEM_FLAG IS NULL OR
RF.RDB$SYSTEM_FLAG = 0)  ORDER BY T.RDB$RELATION_NAME,
RF.RDB$FIELD_NAME
Quote from: alpine on February 02, 2024, 01:40:32 pmBut why this
doesn't apply for the CHARs too?Well... that's just how CHAR works.
Same as ansistring and string[10]. Why are they different?
They just are

Besides... for short strings like 1 or 2 characters, CHAR(1) is more
efficient. They still have their purpose.
BTW. Don't forget Firebird didn't have a boolean type until recently,
so you could use CHAR(1) DEFAULT 'T' CHECK (VALUE IN ('F','T')) NOT
NULL for that.

I still have those as domain in my databases:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
DOMAIN D_BOOLEAN_T  AS CHAR(1) DEFAULT 'T' CHECK (VALUE IN ('F','T'))
NOT NULL^CREATE DOMAIN D_BOOLEAN_F  AS CHAR(1) DEFAULT 'F' CHECK
(VALUE IN ('F','T')) NOT NULL^
QuoteIn practical terms consider just this one rule: only use CHARs if
strings of few characters are to be stored; the exception to the rule
being when working with intermediate tables that are required to
export data to fixed length prn files. Then the fixed length field
will be a positive
advantage.https://www.ibexpert.net/ibe/pmwiki.php?n=Doc.CHAR

----------------------------------------------------------------------
Nicole · 2024-02-03 16:51

Who is interested about the outcome in my case:

I checked all links you posted carefully and my result was, that my
VarChar(2) shall take 6 bytes each.
Those I can substitute by a foreign key of SmallInt, which is 2 bytes.

Firebase got the job to count my tuples and one of my huuuge tables
has about 250.000.
This makes a possible saving a little bit less than 1 MB a table.
The database is about 70 MB.
If I have 2 very huge tables, this means, that a lot of work and a lot
of joins-writing later - would reduce my DB size - by about 2 percent.
I decided to keep 2 MB more and have 2 stand alone tables without
joins and without cascade-needs.

Thank you for all comments of which I hope you enjoyed the exchange
with me and each other as much as me.