Search Tables
Posted: Sun Jul 14, 2019 5:35 pm
ZXDB has a few search related tables, that seem to have been created to facilitate search, but the pattern, doesn't seem to be constant, and I would like a clarification, if possible:
NOTE: Probably an image, would explain things a bit better, but I'm not on my regular computer right now.
So we have "entries" table, that contains "title" and "library_title".
We have an "authors" table, that links the "entries" table to the "labels" table, to provide author names (as label.name).
So far so good.
But then there is a "search_by_authors" table, which has the same links (FKs with numeric IDs) as "authors" table, without the extra fields.
I can only see this as useful, to keep this pattern constant between similar search tables.
The same happens with "search_by_publishers" table, but here there is a difference betwen PKs, since "publishers" has one more field.
So this justifies the the normalization of these search tables. Cool.
However, when we get to "search_by_titles", instead of numeric fields for both FK relations using numeric IDs, we have 2 fields:
- entry_title (VARCHAR)
- entry_id (numeric)
Which is a bit odd, but somewhat justifiable, because "entries" do not use "labels" for the "titles", because they are included in the original "entries" table.
OK, but now I see there is a "search_by_names" ( which is similar "to "search_by_titles"), but relates only with the "labels" table.
What is the context of "labels" ?
So what is this table for ?
It seems that "labels" can name several things, like: authors and publishers.
But what is the context of a "name" in this "search_by_names" ?
We also have "aliases", but that is understood and logical, since it relates with different locales.
Also, why do "labels" have a "deceased" attribute ?
Only people (authors, owners, devs, artists, etc... ) can be deceased.
A publisher shouldn't be possible to be deceased, or can it ?
This is probably related with some legacy data, that still requires this.
Any feedback info, greatly appreciated.
Here looking at you [mention]Einar Saukas[/mention].
NOTE: I understand that these "search_*" tables DO NOT have reals FKs, to the tables, since they are just an utility to simplify searches.
NOTE: Probably an image, would explain things a bit better, but I'm not on my regular computer right now.
So we have "entries" table, that contains "title" and "library_title".
We have an "authors" table, that links the "entries" table to the "labels" table, to provide author names (as label.name).
So far so good.
But then there is a "search_by_authors" table, which has the same links (FKs with numeric IDs) as "authors" table, without the extra fields.
I can only see this as useful, to keep this pattern constant between similar search tables.
The same happens with "search_by_publishers" table, but here there is a difference betwen PKs, since "publishers" has one more field.
So this justifies the the normalization of these search tables. Cool.
However, when we get to "search_by_titles", instead of numeric fields for both FK relations using numeric IDs, we have 2 fields:
- entry_title (VARCHAR)
- entry_id (numeric)
Which is a bit odd, but somewhat justifiable, because "entries" do not use "labels" for the "titles", because they are included in the original "entries" table.
OK, but now I see there is a "search_by_names" ( which is similar "to "search_by_titles"), but relates only with the "labels" table.
What is the context of "labels" ?
So what is this table for ?
It seems that "labels" can name several things, like: authors and publishers.
But what is the context of a "name" in this "search_by_names" ?
We also have "aliases", but that is understood and logical, since it relates with different locales.
Also, why do "labels" have a "deceased" attribute ?
Only people (authors, owners, devs, artists, etc... ) can be deceased.
A publisher shouldn't be possible to be deceased, or can it ?
This is probably related with some legacy data, that still requires this.
Any feedback info, greatly appreciated.
Here looking at you [mention]Einar Saukas[/mention].
NOTE: I understand that these "search_*" tables DO NOT have reals FKs, to the tables, since they are just an utility to simplify searches.