Relationships (1 Viewer)

[plog]

And would each computer (or drive) have it's own table or - everything goes into one (huge) table?

One big table. A good rule of thumb is that tables (and fields for that matter) should be named so generically that someone without knowledge of the system knows what each table/field is for. Also, you shouldn't use suffixes, prefixes or numbers in names. So that means you would never have a table/field named after a specific computer or drive, or have tables/fields like [Computer_1], [Computer_2]...etc. Generic names should rule the day.

Jine don't I want to Join tables? Isn.t that what makes a relatioanal db?

Yes, but in the right manner. Almost all JOINs should be on numeric fields--the autonumber primary ID of one table (parent) goes into the other table (child) and that is how they are joined, not by text like you had done.

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?

Technically no, it only restricts it to one Drive (C it doesn't restrict it by computer because you have no Computer criteria in that WHERE clause. And this also gets back to you incorrectly storing the ComputerName in the Drive table.

Now here's where it gets tricky and goes back to the storing the ID instead of the ComputerName in Drives. If you do not have a Computers table, then you just store the ComputerName in Drives. If you want a Computers table then you remove ComputerName from drives and store just the ID from the Computers table and JOIN them when you need the Computer Name from Computers and all the Drive information. But like I said, I don't see a need for a Computers table because you only have 1 field of data in it. But also again, you are building this piecemeal so maybe in a few hours you will relaize you need more information in the Computers table and that will necessitate one.

 

[June7]

@plog, but if there is no Computers table that will mean repetitively saving computer names in Drives table. Names can get lengthy. Even if there is only the one attribute field (computer name) in Computers table, it is still useful as parent or lookup table. Certainly makes building a combobox simple and reduces data entry errors.

 

[plog]

Combo boxes can be built without a separate table, you can look at existing distinct values in the field to choose from.

 

[June7]

Yes, that is possible (something I have done). However, still not sure it is best approach for OP to repetitively save computer names in Drives.

 

[Cronk]

Good practice (IMO) is to name the ID with the entity eg DriveID, ComputerID.

With only two tables, it may be obvious as to which is which ID but in a database of many tables it helps.

Appologies if I missed somebody already making that point.

 

[David44Coder]

A lot to ponder... Not completely sure about Docs advice about FSO and the four separate fields.
Can come back to that. I've removed the drive name from the Drives table, replacing it with ID and changed the drive letter
field to Len1. I couldn't see how to set it as a byte. then I added another table and query as below.

So next step is add another computer and as Plog says keep DDrive as one big table.(I'll rename it to files). It'll become very big, but okay.
This is correct so far ? The query returns the right thing.

 

[LarryE]

A lot to ponder... Not completely sure about Docs advice about FSO and the four separate fields.
Can come back to that. I've removed the drive name from the Drives table, replacing it with ID and changed the drive letter
field to Len1. I couldn't see how to set it as a byte. then I added another table and query as below.

So next step is add another computer and as Plog says keep DDrive as one big table.(I'll rename it to files). It'll become very big, but okay.
This is correct so far ? The query returns the right thing.

I would do something like this. Of course if you don't need the User table, just drop it and remove the UserID foreign key from the Computer table:

Add specific fields to each table as you require.

 

[Gasman]

Yes, that is possible (something I have done). However, still not sure it is best approach for OP to repetitively save computer names in Drives.

I go scuba diving and I use a program I paid for, to keep track of my dives. It is meant to be able to transfer the dives and all the data from my dive computer. I was having a few issues and was trying to work around them, so went into the DB directly to see how it was constructed. Whilst the program works well, I was suprised at the way it was constructed. However it does the job?

View the Logbook table.

 

[Pat Hartman]

Sorry if that's contrary to advise, but it's interesting to do. (And I doubt I know any better right now)

When you have no idea what you want, you make a lot of false starts and that means rework. If you are willing to toss something you spent 3 days building because you had a better idea, then your plan will be fine. But, if you get attached to things you built which don't support your goal, you are in for a lot of frustration because, saving something that doesn't work, just doesn't work. It is always better to start again. You'll be surprised how quickly the second, third, fourth build go once you have a plan

 

[David44Coder]

Nothings been done that can't be started over. I'm retired so all the time in the world,
A bit intrigued with LarryE suggestion. I've learnt a few things/ideas thanks to this thread.
When initially asked what my aim was I didn't have a good answer, It was mosly to understand/see an example of a relational db.
So some progress.. and an aim has developed - manage backups- be able to see, compare, find duplicates etc.
I'll stop what I'm doing to try Larrys way. Knowing joins are on numeric fields helps and I like the seperate table for folders.
There's duplicate folders as backups groiw. I stend to start a new one and keep the old in case it's needed.
What would be really handy now is to see how items in the 5 tables are extracted in querys. I know the infinty symbol and "1" in the join diagram have some significanse,but I forget what. Will dig into that.
@Larry I want to (but could change) identify each drive by it's serialnumber. Some have >1 volumne. Which table should have the serial number field?

 

[Mark_]

@Gasman, you've got me jealous now. I haven't been diving in almost 10 years and miss it!

 

[Pat Hartman]

Nothings been done that can't be started over. I'm retired so all the time in the world,

That is a unique attitude for a non-professional. Most novices are reluctant to ever get rid of code they've written whether it does what they want or not

 

[LarryE]

Nothings been done that can't be started over. I'm retired so all the time in the world,
A bit intrigued with LarryE suggestion. I've learnt a few things/ideas thanks to this thread.
When initially asked what my aim was I didn't have a good answer, It was mosly to understand/see an example of a relational db.
So some progress.. and an aim has developed - manage backups- be able to see, compare, find duplicates etc.
I'll stop what I'm doing to try Larrys way. Knowing joins are on numeric fields helps and I like the seperate table for folders.
There's duplicate folders as backups groiw. I stend to start a new one and keep the old in case it's needed.
What would be really handy now is to see how items in the 5 tables are extracted in querys. I know the infinty symbol and "1" in the join diagram have some significanse,but I forget what. Will dig into that.
@Larry I want to (but could change) identify each drive by it's serialnumber. Some have >1 volumne. Which table should have the serial number field?

If it's the drive serial number, then in the Drive table. Your Capacity would also be in the Drive table if you are keeping track of a Drives capacity. If you are tracking Drives capacity, then a DriveCapicity field in the Drive table would be appropriate. Just remember one thing about ACCESS...each piece of information is held in only one field in one table. Here's the file itself so you don't have to start all over

 

[Pat Hartman]

Don't confuse the PK/FK relationships which are what are used for joins with search criteria. You have an autonumber which is defined as the PK and you can also have a unique field which is text and that would be the field the user searches on. The joins to the child tables are always on the PK/FK and never on the text field. The text field is changeable and occurs once and only once in any application. The PK is repeated as the FK in every child table so occurs many places.

 

[David44Coder]

Wow! This is quite something,Very differnt. I've been hours building the tables/relationships for the first time ever. Access doesn't let you make a mistake and you have to think in numbers. Drive F is now a number dependent on another number etc.
I was sure the Drive table should come before Volume, then changed my mind twice.. Finally I'm at the Folders table.
I wanted to add Serial numbers which became a bit of a minefield, and not sure it's right. I see now Larry you mentioned it (and capacity which I'll leave for now). I'll get your attachment. Slowly getting what you mean by each piece of information is held in only one field in one table and what Pat said about Joins with PK/FK only. My relationship display now matches Larrys (with one more right or wrong) and I caught up with 1 to many infinity thing and enforcing some integrity to bring them into view.

 

[LarryE]

Wow! This is quite something,Very differnt. I've been hours building the tables/relationships for the first time ever. Access doesn't let you make a mistake and you have to think in numbers. Drive F is now a number dependent on another number etc.
I was sure the Drive table should come before Volume, then changed my mind twice.. Finally I'm at the Folders table.
I wanted to add Serial numbers which became a bit of a minefield, and not sure it's right. I see now Larry you mentioned it (and capacity which I'll leave for now). I'll get your attachment. Slowly getting what you mean by each piece of information is held in only one field in one table and what Pat said about Joins with PK/FK only. My relationship display now matches Larrys (with one more right or wrong) and I caught up with 1 to many infinity thing and enforcing some integrity to bring them into view.

Serial numbers and capacities are only characteristics. That's all. They should be a field in their respective tables only. They are informational descriptions.

 

[David44Coder]

So NOT like this Larry?

 

[LarryE]

So NOT like this Larry?

Not like that. And never use just Id as a primary key. See the design I gave you. Note all primary keys have descriptions. So the primary key for the computer table is ComputerId not just Id.

 

[June7]

I advise not to use same name for primary and foreign key fields. One way to fix that is to use suffix, like: ComputerID_FK.

 

[David44Coder]

When I went to change 'ID' there was no rename option so I figured it might be a Access Generated Read Only field.
Now I see I can overtype it. LOL ah well, can't win them all.