[solved] Best practise - foreign key versus own field

[Nicole]

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/fblangref40/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]

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

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

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

My 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)

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?

Again no.
You can define Foreign Keys allowed to be NULL (so called "orphaned" child-records)

[Zvoni]

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]

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)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][+][-]

1. SELECT p.ProductName, pu.Purchased, s.Sold
2. FROM Products p
3. INNER JOIN Purchase pu ON p.ProductID = pu.ProductID
4. INNER JOIN Sale s ON s.ProductID = p.ProductID

Can I use foreign keys instead of like on p.ProductID = pu.ProductID ?

[alpine]

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

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

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]

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

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

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]

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

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

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]

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

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]

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

Sucker bet.... 

[Joanna]

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]

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.