Well, I’m talking here, because I remain perplexed despite all the reading I’ve done, including Mariano Casanova’s tutorial.
.
I have a TFilmsSeries table with the following columns:
FilmSeriesID, IDCategories, IDPlatforms, TitleEnglish1, TitleFrench, Viewing, LinkAsianWiki, LinkNetflix, LinkRakuten, Year.
.
I recently added columns to this table, namely the following columns: Number of Episodes (Integer), Number of Episodes (Integer), Number of Days (Integer), Availability (Date).
.
The question I ask myself now is that, apart from the NumberEpisodes column, should the other three constitute a new table, because the relevance of these columns is linked to the Viewing column which is a check box and which allows you to determine if a film or series has been viewed or not.
.
In fact, these columns allow you to keep up to date regarding the availability of an episode
.
So I am confused, in table normalization, does this kind of situation require a new table or these columns can just be added to the table currently without normalization problem.
.
I’m trying to see what would be the best option if adding a new table is a possible option
.
Does anyone have any insight into this?
If this would be my database of episodes I have seen, I would add the information directly to the table. An exception would be, if the table of episodes was copied from something like IMDB. Then I would not change/extend the table.
.
I would usually work with 3 tables:
- public info on the episode/film etc.
- history of my personal viewing
- availability where/until date/link
Besides that I think you missed one column or copied one twice:
To me, this looks like a one-to-one relation where each series has zero or one set of extra attributes.
TFilmSeries [FilmSeriesID (PK)] 1-----1 [FilmSeriesID (PK)] TExtras
In a form you would link a subform with extra inheriting the PK from the parent form having the series.
This relation is not symmetric. The series (possibly having an auto-ID) comes first, then you attach the extras with an inherited PK.
It’s quite interesting, Wanderer and Villeroy have two totally different perceptions. At least, that’s what I deduce. Either one would be able to meet my needs.
.
On the other hand, I like the fact that a series of films can be associated with zero or one set of additional information, and each set can be associated with a single series of films.
.
We are therefore talking about a 1 to 1 relationship between two tables, however it appeared to me that the only way to establish this relationship was using a junction table. So, in addition to the FilmsSeries table that I already have, I would need a table for Extras and a table that helps establish this relationship. I am not at fault in writing this. Is it correct?
No junction table.
CREATE TABLE "EXTRAS"(
"Episodes" tinyint not null,
"Days" smallint not null,
"Availability" date not null,
"ID" integer primary key,
foreign key ("ID") references "TFilmsSeries" ("FilmSeriesID")
)@Villeroy, I created the table with the SQL statement you suggested by modifying the column names to make things work with my DB.
.
The table that I call “TViewings” was created correctly.
.
If I understand correctly, you are implying that this “TViewings” table would or should be associated with a subform of the parent form which is linked to the “TFilmsSeries” table? Is it correct?
.
If I wanted to create a new form whose role would be to display only the records whose check boxes are not checked in order to more easily manage the extras linked to these records. I would therefore need a query linked to this new form in order to display certain fields from the “TFilmsSeries” table and the “TViewings” table.
.
Would the relationship between these two tables as you proposed allow this?
Main form: TFilmsSeries (or anything providing a valid series ID).
Subform: TViewings with a link from the FilmSeriesID to TViewings.ID.
I have no idea what you need any checkboxes for.
SELECT * FROM "FilmSeries" WHERE "FilmSeriesID" NOT IN (SELECT "ID" FROM "TViewings") selects all columns from the film series having no record in the viewings.
You can have your checkbox generated from existing information. Use this for your parent form and make the boolean field read-only to avoid error messages.
SELECT "TFilmSeries".*, "TViewings"."ID",
CASE WHEN "TViewings"."ID" IS NULL THEN False ELSE True END AS "Viewed"
FROM "TFilmSeries" LEFT OUTER JOIN "TViewings" ON "TFilmSeries"."TFilmSeriesID" = "TViewings"."ID"
I understand that I am not very clear in my requests and explanations.
.
I will try to clarify things. They say a picture is worth a thousand words so see a screenshot:
.
Currently I can achieve what I want using the “TFilmsSeries” table. All fields of the “FilmsSeries” form are clearly identified, including the fields which are only used by the “Visionnements” form which are “TitreAnglais1”,. “Visionnement”, “NoEpisode”, “NbJours” and “Disponibilite”.
.
The check box of the “TFilmsSeries” form allows you to know which series have not been viewed. The viewing form therefore only displays the series not viewed and it allows you to indicate an episode X will be available in X day(s) therefore on date X. The goal is to have follow-up regarding these series currently being viewed.
.
However, I find or I should say that I think that the 3 columns “NoEpisode”, “NbJours” and “Disponibilite” take up too much space unnecessarily in the “TFilmsSeries” table, so I like Villeroy’s idea to have a table dedicated to these 3 columns was probably better at the organizational level.
.
The reason I want this in a new form is that I don’t want to have to completely reconfigure the “FilmsSeries” form to make space for a subform and additional fields.
.
Being lazy by nature, I figured it was simpler to create a new “Viewings” form to do this tracking. That’s what I did.
.
Now I want to take the table that Villeroy suggested and use it in relation to the “TFilmsSeries” table for the “TitreAnglais1” and “Viewing” fields and the fields of the new table that I called “TVisionnements” so that everything can work with the “Visionnements” form.
Unfortunately, pictures tell nothing unless you have a display issue.
series_viewings.odb (13.4 KB)
The form starts with series filtered by those which have not been viewed yet (series 3 and series 4). On the toolbar you can turn that filter on and off.
Thank you for your time.
.
I will take the time to take a look at all of this. I will see what I do regarding the use of a table dedicated to these columns.
.
For now I’m getting the result I want by adding these columns directly to the table like Wanderer mentioned.
.
Good day!