DB Concepts and how to

[Middlemarch]

What I read about relational databases has never made a lot of sense to me but I have a dataset now that might lend itself to this method and be a good leaning if I can get a bit of guidance.


There 3 fields, Title, Artist, Release and job is to extract Release x with a certain Artist and Title.


I think this becomes three tables with the Primary Key of one (but which?) being a foreign Key in the other two.


Is that right ? If yes, how would you construct a query ?

[ArviLaanemets]

It looks like some film or video DB. So like
tblVideos: VideoID, VideoTitle, ...;
tblArtists: ArtistID, Forename, LastName, ArtistName, ...;
tblVideoReleases: VRID, VideoID, ReleaseDate, ...;
tblVideoReleaseArtists: VRAID, VRID, ArtistID, ArtistRole, ... (in case same artist has several roles in release, there will be a separate row for every role - with same values of VRID and ArtistID)

To get a list of all releases of certain video certain artist was casting in, and his/her roles there, you can have something like:

SELECT v.VideoTitle, vr.ReleaseDate, a.ArtistName, vra.ArtistRole 
FROM ((tblVideoReleaseArtists vra LEFT JOIN tblVideoReleases vr ON vr.VRID = vra.VRID) LEFT JOIN tblVideos v ON v.VideoID = vr.VideoID) LEFT JOIN tblArtists a ON a.ArtistID = vra.ArtistID
WHERE v.VideoTitle = [SomeVideoTitle] AND a.ArtistName = [SomeArtistName]
ORDER BY vr.ReleaseDate, vra.ArtistRole

NB! This DB structure allows to have several roles for same artist in release, and different sets of roles for this artist in different releases of same video!

[Middlemarch]

Thanks very much, about to have a go constructing and see how I get on.

[Middlemarch]

Is it a problem if Duplicate VideoTitles exist?
Would they each get an ID, or remove any duplicates leaving just one ID?

[ArviLaanemets]

When all releases have same title, then the single title defined in tblVideos is OK.

When it is possible, that any of different releases can have different title, then you must have the Title field in tblVideoReleases instead, or you must add tblTitles table, and have TitleID in tblVideoReleases (and you have to link tblTitles to tblVideoReleases in query too).

In case different videos can have same name, this will be a tougher one. You have to differ them in some way - e.g. by producer, or by film director, or whatever - as an additional field in tblVideos. And then you have to add this additional condition into query too

[Middlemarch]

I think the latter is true, some videos have the same name, but will always have a different artist. Would adding a field to tblVideos holding the ArtistID (Just for the duplicates) be feasible?

[ArviLaanemets]

Now I'm really confused!
Let's assume you add artist name/ID into tblViedos as identificator. We have earlier estimated that in new release of same video, at least some artists can be different. What happens, when the artist you used to identify the video earlier, is replaced in new release? It is used to identify the parent table of release, this artist has no connection? Or you have to define this release as start release of new video, i.e. add new video, with some other artist as identifier, and as result not having any connections with previous one? Or you have to change the artist in tblVideos to some which is presented in all releases (but there may not be such one present - the simplest example of this will be a video with single artist, which was now replaced!)? As you see, having simply some artist, who parcipiated in some casting, as identifier is a slippery solution!

You can look at this in this way:
Table tblVideos contains top set elements of your database. Table tblVideoReleases contains subsets of tblVideos elements. Table tblVideoReleaseArtists contains subsets of tblVideoReleases. Having some sub-sub-set element defining the whole top set element is a no-no for database design! It is like some cleaning lady giving orders to your company boss (I'm not saying this can't happen )

[orange]

Do you have an example of what could be a duplicate? It might help readers to advise if they could see the data in context.
I agree with Arvi's comments. You would need some additional unique info to distinguish one "duplicate/replicate" from another(s).

Here is a video on Database Normalization by Decomplexify that may be helpful with concepts.

[Middlemarch]

I watched half of that link orange before realising this stuff is beyond me. I was hopeful when he used The Beatles example but just as you think something might be sinking in you lose the plot completely. I didn't really follow ArviLaanemets last message to well either. My apologies.

But I'm still hopeful of getting this working. I have all the data so there's nothing to insert or delete. Does that simplify anything ? The goal is to query by title and artist, and get the releases. I believe it true to say each artist can have many titles, and each artist and Title can have many releases.

My intent is build the tables as laid out by ArviLaanemets. So far I have three with a PK autonumber and second field holding the artist, title or release. Some of the titles were duplicates where all but one were removed. No artists are duplicated.
There's lots of releases and maybe duplicates (Where two artists feature in the same release. That may or may not matter.)

I suspect the third table (tblVideoReleases) should have a third field containing the autonumber in tblVideos.
Then figure some way to build tblVideoReleaseArtists, probably using VBA as a helper.

Should I keep going or am I so far off track it'd be best to can the idea ?

[orange]

Well setting up your tables and relationships is critical. I'm going to suggest another link that works through an example. I found this link years back, and the procedure he uses made sense. I have suggested the link and procedure to many others who have offered positive feedback.

This is the link to the zip file at RogersAccessLibrary containing the sample material concerning ZYX Laboratories. Within the zip is a structured word docx file. He leads you through a procedure from a narrative/description of the business, the processes and the "things involved", through a series of steps to identify your Tables and Attributes leading to a normalized, well structured data model.

Below is an overview of the "Hernandez Process" he is using. I strongly recommend you spend 30 to 60 minutes with the ZYX example. You will experience database design, data modelling and normalization. What you learn can be used with any database. Best use of 30-60 minutes!

Good luck.

NOTE: If you find that the zip file is Blocked, see this Unblock Mark Of The Web link to the article by Crystal Long.


One of the best database design books of our time is: Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design By Michael J. Hernandez.

Unfortunately, Hernandez goes into such great detail at times that the overall picture is lost. The following is a list of the major steps in the process. This list should not be use in place of reading the book. It is meant only as an adjunct to it.

1. Create a narrative that accurately and in some detail describes the business
2. Double underline each Subject and Direct Object in each sentence
3. Single underline all the rest of the nouns that describe the subject or direct object
4. Make a list of the Subjects. Try to determine which are duplicates or are not pertinent. This will be your Subject List
5. Make a list of the other nouns. Try to determine which are duplicates or are not pertinent. This will be your Preliminary Field List (PFL)
6. Group the fields in the PFL into logical groupings. Do each of these groupings have a corresponding value in the Subject list? If not, you missed a subject so you should add it.
7. Go through the revised Subject list to see if there is any data that you should be storing about that subject that you are not. If so, add it to the field list.
8. Place all the Subjects across the top of a sheet of paper and write the corresponding fields below them, check them off the list as you do.
9. Now look at your tables and fields and compare them to the Ideal Table and Ideal Field:

Elements of the Ideal Field
--------------------------------------
It represents a characteristic of the subject of the table.
It contains only a single value
It cannot be deconstructed into smaller components
It does not contain a calculated or concatenated value
It is unique within the entire database structure
It retains all of its characteristics if it appears in more than one table.

Elements of the Ideal Table
----------------------------------------
It represents a single subject
It has a primary key
It does not contain multipart fields
It does not contain mulivalued fields
It does not contain calculated fields
It does not contain unnecessary duplicate fields
It contains only the minimum redundant data

1. Create Primary keys and identify any Alternate Keys (see Hernandez)
   
   1. Note: Most tables will have Artificial Primary keys (Autonumber in MS Access).
   2. Alternate keys are used for Unique Indexes.
   
   
2. Identify relationships between the tables. Be sure you can identify it in both directions:

Each Customer can have One or More Orders
Each Order can pertain to One and Only One Customer

1. Resolve any Many-to-Many relationships with the addition of a Linking table and two One-to-Many Relationships.

Order >----------------------< Products
(Each Order can have Many Products)
(Each Product can be on Many Orders)

Order ----------< OrderProducts >---------Products

1. Create your relationships, put the Primary Key of the table on the "One" side into the table on the "Many" side.

One-To-Many
Customer Order
--------------- ----------------
CustomerID (pk)------| OrderID (pk)
CustomerName |-------< CustomerID (fk)
...other fields OrderDate
...other fields

Many-To-Many
Order OrderDetails Products
--------------- ----------------- ----------------
OrderID (pk)--------<OrderID (cpk)(fk) |---- ProductID
OrderDate ProductID (cpk)(fk)>--| ProductName
...other fields ...other fields