Several attributes in one category

[JonahThrane]

Hello, my name is Jonah, and I am attempting to make a database of teaching materials.
We have some material that can be used in more than one class, and I would like to make it so you can search for the class and find the material needed, these are physical items, i have pictures in the database, and i have their location listed with what room they are in, then which cupboard and then which shelf. The search function is made and works, its a simple Me.Filter code in VBA.



To start I have made a Tabel with some basic information about the item, including the class numbers it applies to, which is linked to a table including the class numbers and names to make it easier in the forms.
What I would like to be able to do is add more classes per item. Eg: if the teaching material is applicable in classes 1.1 and 11.2.1 I would like to be able to list it as such rather than having to make two or more of the same item in my table but for different classes.

I hope I have explained myself somewhat well enough. I would be happy to include the file but it wouldnt include the images for the database and it was too big for the 500kb limit.

Thank you for any help and tips you can give me.

[Welshgasman]

You would use a junction table between Materials and Classes.

Woould still have one record per Class per Material.

[JonahThrane]

Woould still have one record per Class per Material.

And thats what I am trying to avoid

[Welshgasman]

And thats what I am trying to avoid

I do not know any other way of doing it?

[Micron]

I wonder if the advice given has been correctly interpreted. Jonah, if you have not reviewed db normalization I suggest you do so first. The idea that you should not repeat information just so you can show additional but different aspects about it is correct. Junction tables is one way to take care of that problem.

[orange]

Material-->MaterialForClass<--Class

This is a common table structure to deal with a Many to Many relationship.
WelshGasman and Micron have suggested this also.

[CJ_London]

I agree the many to many approach is the only viable way. However I have to ask - do you never have the situation where materials are stored in more than one place? for example perhaps pencils and pads are in every classroom or perhaps in cupboards in several corridors

I would be happy to include the file but it wouldnt include the images for the database and it was too big for the 500kb limit.

It is generally not advised to store pictures actually in the database, store them in a folder and in your database store the path - which might only need to be the file name if the pictures are all stored in one folder.

[mike60smart]

Hi Jonah
Can you upload what you have so far?

[JonahThrane]

I agree the many to many approach is the only viable way. However I have to ask - do you never have the situation where materials are stored in more than one place? for example perhaps pencils and pads are in every classroom or perhaps in cupboards in several corridors

It is generally not advised to store pictures actually in the database, store them in a folder and in your database store the path - which might only need to be the file name if the pictures are all stored in one folder.

Pencils are not teaching materials. "Teaching materials" are materials used in relation to teaching about a subject, spare parts and similar count here, not pencils.

And thats what i have done so far, I have pictures in folders and linked them in by typing the file name.

[JonahThrane]

I will see what i can do, if i do it would have to be an external link i upload, because its too big for this platform apparently.

[Welshgasman]

I will see what i can do, if i do it would have to be an external link i upload, because its too big for this platform apparently.

You can upload a subset, as it is the structure that is the most important.
Even an empty DB or nearly empty would help.

[CJ_London]

also before loading, compact repair and then zip the file