[solved] Best practise - foreign key versus own field

[Nicole]

On reconstucting my database I wonder this:

I had a table

Code: MySQL  [Select][+][-]

1. CREATE TABLE TBKALENDER
2. (
3.   JDATUM date NOT NULL,
4. ....
5.   CONSTRAINT PK_TBKALENDER PRIMARY KEY (JDATUM)
6. );

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]

On reconstucting my database I wonder this:

I had a table

Code: MySQL  [Select][+][-]

1. CREATE TABLE TBKALENDER
2. (
3.   JDATUM date NOT NULL,
4. ....
5.   CONSTRAINT PK_TBKALENDER PRIMARY KEY (JDATUM)
6. );

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]

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]

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?

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]

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]

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?

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]

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?

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 from IRC]

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]

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]

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.

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]

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]

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.

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]

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

Yes. 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 from IRC]

@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]

@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.