Relationships

[David44Coder]

This question may be beyond what's expected on the Forum but never having figured it out, I'm curious about database relationships.
Northwind and apples/pears/customers etc sort of loses me but I have a real life example that (should it work) might be a good learning tool.

My concept is a table with HD serial numbers, then sub? tables with Drive letter, Volume names and Size.
Should it get the far then I'd probably be able to add folder and filenames
How should I start? So far I've a list of data. Serial numbers, drive letters, volume names and sizes
Is the above a good basis for RD? If not I'll put it together some other (my) way.
Thanks for any pointers.

 

[plog]

In general, entities and properties become tables and fields respectively.

Cars are things and become a table. Make, model, year and color are properties of cars and become fields in that table.

An overview of what you hope to use your database for would be helpful. With what you've given us, I only see 1 table--HardDrives. It has a SerialNumber and Size fields. Now drive letter and volume name can be tricky because I think those can be different among computers connecting to them (my F drive could be your G drive, etc.). So an overview would be helpful.

 

[David44Coder]

Interesting. Do I have an overview? Goal is to find/identify backups. But mostly I'm just going where this takes me.
Your drives won't affect mine and vice versa. Yes/No? I'll show what I've cobbled together so far. With no idea if I'm going in the right direction!

 

[The_Doc_Man]

@David44Coder -

In the most common relationship, you have a table of something that is independent i.e. it is a thing unto itself. Then you have another table of things that are dependent because they are part of something that holds them, encapsulates them, gathers them... a one-thing/many-things type of relationship. (It's the most common type.)

In the attachment to #3 you show us drives on a computer based on drive letter. Your list is almost a table of the drives currently in your computer. Yes, I said "almost." To get all the way there, you need the computer name, call it "XYZ". Suppose you want to expand your list to include at least one more computer in your data/storage inventory. To express the relationship of multiple drives to multiple computers, you would need one more column that contains the computer name - such as "XYZ" for each drive that is on that computer. That would be the final field needed to develop a formal relationship.

Now, if you had another computer, call it "ABC", and you had a list of drives for it, you would need TWO tables - one for computers and the other for drives. You would have two records (at first) in the computer table - "ABC" and "XYZ" - plus whatever is specific to the computers (BUT NOT THE DRIVES). Things like RAM size, RAM speed, clock speed, number of cores, number of threads, etc. would be properties of the computers. To set up the formal relationship, you need a second table that lists ALL of the things you listed in your exhibit in #3 post, plus one more field - the name of the computer where that drive is found. Each record in the drives table would include the name of the computer hosting the drive.

NOTE: Purists would assign each computer a unique ID number and use the ID number in the drives table rather than the name. Since this example is for explanation purposes, I will keep it simple.

Now you would have what you need to declare a formal relationship. The drives table would be dependent to the computers table. The use of the computer name in the drives table ties a given drive's record to a given computer's record and establishes a parent/child relationship. In the right kind of query, you would say that the COMPUTER and DRIVE tables could have a JOIN ON ComputerName.

Access exploits this relationship by knowing that you have placed the computer ID key in TWO places - once in the computers table and once in each related record in the drives table. By updating the Relationships list that is one of the Access Tools to show the computer name in the computer table and also in the drives table, you make it a formal relationship that Access can exploit where appropriate.

Does that help you with relationships?

 

[June7]

Your posted table presumes a single computer and one set of associated mapped drives.

The_Doc_Man describes a parent/child relationship - each computer can have multiple drive letters and each drive letter can be used for multiple computers. If you want to save detail info on computer properties other than the drives, that entails two tables at least: tblComputers, tblComputerDrives - record in tblComputers must exist for related records in tblComputerDrives.

Other tables for Drive letter, Volume names, and Sizes would be "lookup" tables to assist with data entry into tblComputerDrives but are not required. Comboboxes set for LimitToList Yes facilitate standardizing data. Combobox can use lookup table as source for list or list can be hard-coded in combobox properties.

In either arrangement, it is customary to save parent or lookup table record ID into tblComputerDrives, not the actual text value of "XYZ" or "Movies 2". This is to conserve space and speed sort/filter processing, especially in large databases. However, if the text is minimal, such as A, B, C, etc, saving text may be adequate and eliminates a need for a JOIN in query.

 

[plog]

From what I see it mostly looks good and only needs 1 table. The only advice I have is work toward numeric field when can. Capacity should be numeric and in a constant unit (terra or gigabytes) and if Disk Management always has a format of 'DiskX' where X is a number, make it numeric and just store the number.

Other than that you realy don't have a lot of data to necessitate a database.

 

[David44Coder]

Hi Doc_Man, yes it does and thank you for such a good description.
I do have other computers, so I expand your list to include a computer name and add another table for computer names.
Each table has a common field which is used to join? Or something like that..
As I do it it should become clearer. I wasn't thinking of adding RAM speed, clock speed etc (but I see where they'd go) but more filenames and folders.
Which would be another table I suspect. Without getting too far ahead, would these have records for each filespec, or each subfolder have it's own field? And a list of root folder files somewhere else.

 

[David44Coder]

Thanks @june, plog?
>you realy don't have a lot of data to necessitate a database.
It may be when files are added, but small is an easy start?

 

[June7]

Listing folders and subfolders - to what depth? Why are you trying to replicate Windows file manager?

 

[David44Coder]

@june because i like my own way of doing things. And to see and .compare backup files etc.
Admittedly this is new territory and I don't know what I'll end up with... but if nothing else I'll learn a bit more.
Then I'll only have to remember it LOL.
What I have now is


SELECT Computers.ComputerName, Drives.[Drive letter], Drives.[Volume Name]
FROM Computers RIGHT JOIN Drives ON Computers.ComputerName = Drives.Computer
WHERE (((Drives.[Drive letter])="C:"));
Is this correct ?

 

[plog]

No its not right.

Computers and Drives should not JOIN on ComputerName. You should have the ID field of Computers to JOIN them.

However, you don't really need a Computers table at all if it only has one field. Instead, just put the Computer Name in Drives as you have it now. Also DiskManagement still doesn't seem numeric. You took the letters out but the data type is still text

 

[The_Doc_Man]

What you did seems to be potentially functional. I deliberately kept my earlier description simple because you claimed to be unaccustomed to relationships. However, note plog's comments.

Since you are using a JOIN, you could have used the computer's numeric ID as the link for the drive, so you would have a PK of the computer's ID number rather than its name. In the JOIN, you could then have retrieved the computer name at the same time that you retrieved the drive info. It is fair game to select from EITHER contributing table in a JOIN query.

Let me emphasize that EITHER WAY works - but because the numeric key is simpler and easier to type, and probably quite a bit shorter than text names, using the computer ID as the linking field for that relationship is MORE EFFICIENT. Using names is NOT WRONG - but it is less efficient because of potentially using more bytes in the linkage. Your two computer names are longer than 4 bytes, which is the length of a LONG integer. And there are computer instructions that can compare up to 4 bytes at once. This is an efficiency issue ONLY.

Now, as to the ID number on the drives, here is ANOTHER efficiency issue. You have unique numbers on each record in the drive table. That is NOT WRONG. But again, as a matter of efficiency, you might not need that ID. Here is the test: Are you planning a third table to list files in a way that would require you to remember which drive they are on? Doing so would turn the drive table into being both a child (of the computer table) and a parent (of the proposed file table.) Perfectly legal if you want to do that. If those drive numeric IDs are unique, you could use them as the drive table's prime key - and the PK is always the best parent field to choose when establishing a relationship to a child table.

However, if you were NOT really planning to track files in a "grandchild" table, that ID is not needed for anything. It still wouldn't be wrong to keep the number around - but it might not be necessary.

Finally, if you want to have info on files in a table, things will get very big very fast. The file path, name, and type (even if you strip off the drive letter) can be quite long. Which means you would need the file path/name combo to be a SHORT TEXT field of 255 bytes. Given that a Windows computer has a highly cluttered C drive, on the order of tens of thousands of Windows O/S files, just consider the magnitude of your potential data set. But as a learning exercise? Go for it. Just don't expect to want to keep it around that long.

 

[David44Coder]

Thanks @plog that's what I need to know,
I've changed DiskManagement data type to Numeric,
Do I keep the Computers table? getting conflicting advice on that.
And Join on ID field of Computers. That's 1 but there's no corresponding field in Drives. Or does Access know some other way?

 

[June7]

You keep Computers table so you don't repetitively save computer name in Drives table. As I pointed out in post 5, saving text might be acceptable for short identifiers like gender (M/F) or Y/N or drive letters. Otherwise, save record ID into child table, not the computer name - unless you really want to repetitively save computer name. The only advantage is eliminating a JOIN in query to retrieve computer name. The two computer names you show are relatively short - will that always be the case?

Doesn't matter that Computers table has only the one attribute field. That should not be deciding factor.

Would need integer number field in Drives table to hold the computer ID, named like CompID_FK, and remove the computer name field.

I agree with Doc - I expect you will quickly get frustrated and abandon tracking folder structure.

 

[David44Coder]

This is great Thanks everyone for the feedback,
So all my Text Fields in Drives which were "WIN10-PC" are now a numeric with value 1. (matching Win10-PC autonumber in Computers).
SELECT Computers.ComputerName, Drives.[Drive letter], Drives.[Volume Name]
FROM Computers RIGHT JOIN Drives ON Computers.ID = Drives.Computer
WHERE (((Drives.[Drive letter])="C:"));

@june I didn't quite follow about "eliminating the join".
Doc, your test about planning a third table to list files in a way that would require you to remember which drive they are on I think is Yes.
I want to query a file or folder and see where they are. I have backups of backups and probably can remove/update many of them.
If this all works there will be 3 computers. Also some drives in caddies that aren't connected until needed., and some shared via LAN.
I (for now) identified them in the Location field. Can change to suit as needs arise.

I will probably exclude C: or the Windows folder. It may still become quite big, but doesn't have to be fast.
My next step is to create a table of files. Initially using full paths (as this seems easier) .
Then see if I can write a query to show something useful - just to the debug window.

 

[The_Doc_Man]

While there is a slight learning curve to use it, may I suggest (if you haven't seen it already) that you look up "FileSystemObject" - which is written as one word for online searches. It is part of the Windows Scripting Library and lets you do VBA-based file searches - including selective searches - for files, which would let you determine things like file-path, size, file type, creation date, and a few other options.

FileSystemObject (or FSO as we often abbreviate it) lets you build a "model" of your file system by creating Folder and File objects that can be examined. Folder objects contain a collection of file objects - and of course a folder is also a file. There are some difficulties in keeping track of things if you want to do recursive searches - but they CAN be done with careful programming.

 

[June7]

I didn't quite follow about "eliminating the join".

If you saved computer name into Drives, would not need to join tables. Query would be like:

SELECT * FROM Drives WHERE [Drive Letter]="C:";

Are you really saving the colon with drive letter - why?

Strongly advise not to use spaces nor punctuation/special characters (only exception is underscore) in naming convention - better would be DriveLetter or Drive_Letter and VolumeName or Volume_Name.

 

[plog]

It kind of seems like you are just building this by the seat of your pants and seeing where it takes you and hopefully that ending point provides you with something helpful. Not a criticism, just an observation.

My advice would be to start building this from the other end--not with what you have, but with what you want--the end goal. Mock up a finished report or form systems that would actually help you with something. It doesn't have to work or even show real data, but be something you would actually use whenever this whole thing is built.

With that it will tell you the data you need to start with, then we could help you normalize tables for that, then we could move on to queries and reports and then the forms you would need.

Its really hard to help you with the way you are building it now--piece by piece with new pieces being revealed to us infrequently and no real idea of where you want to head. So think of where you want to end at and that helps us help you in a more efficient manner.

 

[David44Coder]

Yes it is being built piecemeal, seems a reasonable plan when not sure exactly what might all entail. As it develops I (expect to) get a better idea of what I want, or what's possible, Sorry if that's contrary to advise, but it's interesting to do. (And I doubt I know any better right now)
I hope my next question doesn't upset the applecart. I envisage a 3rd table with a field for filepecs and another containing the ID from Drives for that particualar drive letter. Is this corrrrect? Should I do this? And would each computer (or drive) have it's own table or - everything goes into one (huge) table? Is one method better for searches?
I'll read up on FSO (I have seen and used it verbatim e.g from chatGPT). and adopt Junes naming conventions. Jine don't I want to Join tables? Isn.t that what makes a relatioanal db? If just SELECT * FROM Drives WHERE [Drive Letter]="C:"; doesn't that restrict it to one Drive/Computer with no need for a Computers table?

 

[The_Doc_Man]

If you use FSO, you CAN get a complete file spec out of it, which would already include the drive letter. You CAN use FSO on a file spec to separate the drive, path, name, and type into four separate fields.

What you want to avoid is overkill. Usually because it is a space-waster and also slows you down to have to diddle with long strings. If you want to split out a full file spec into its parts, fine. It is clear that you are doing this as a self-teaching exercise, which I understand and actually applaud. "Learning by doing" is a time-honored way and is why computer classes give coding assignments as homework. But if you have a fully qualified file spec (all four parts) then you would NOT include your drive-letter numeric key as a separate field as well because in that case it would be redundant. What you are doing risks running afoul of redundancy issues by duplicating data. You would be able to read on ways to avoid such redundancy by studying database normalization. Generally, the rule is that if you need to "assemble" the parts to use it, all the parts would be in the same place. Therefore, you COULD take apart the four pieces of a file spec and store them in four different fields, but to actually use the actual or implied file spec, you would have to put the parts back together.

There is another consideration. Remember I mentioned that there is an efficiency factor. For computer names, they tend to be long enough that you want to use a numeric key because it is usually shorter. But there are exceptions to the "numeric key" rule. As it happens, since drive letter is a single byte (because you assume the presence of the colon and therefore don't have to store it), you could profitably use the single-byte drive letter as a secondary key in which case, the numeric computer ID plus the drive letter becomes unique as a way to specify a single drive anywhere in the list of computers you are tracking using a total of 5 bytes (vs. 8 if you stored the drive-letter numeric key). Study topic: Compound keys.

Also note, however, that there are other ways to track a drive. As a secondary bit of reading, look up UNC = universal (also unified) naming convention, which would skip the drive letter and instead use the drive's proper name in your system. Drive letters/path combinations are stand-ins for UNC addresses leading to external networked drives. A UNC looks like "\\computer name\drive name\path name\" and it can be tricky to determine a local drive's UNC unless you have a drive-share set up. However, the CMD prompt would show you the mapped drives after the command NET USE. That command DOES NOT show you local drives - but you indicated you might have some networked drives, so they would show up in that context. I'm not saying you would use the UNC in your case - but you might run across it since you ARE diddling with remote as well as local drives.