Begginer Relationships Question

[dluga20]

Hello All,



I am a beginner (and yes I have already watched many online tutorials so please don't point me to any) and I am trying to figure out how to set up some relationships. Can someone show me an ERD and/or explanation of the description below? I can't seem to figure it out despite it seeming so simple.

I want to build a DB containing part numbers, fixtures, and gauges. Any part number can have any number of fixtures or gauges. For example, part number 123 may have 2 fixtures and 1 gauge associated. Part number 456 may have no fixture and 2 gauges associated with it.

[CJ_London]

you need two tables, one for parts and one for fixtures and gauges

if a fixture or gauge can be in more than one part then you need a third 'link' table

please clarify and we can move on to what those tables need to look like

[ranman256]

Yes, 2 tables.
tFixtures would have a code fld, FIX or GUA
And this table can self relating...with a [parent] ID, if a fixture has sub fixtures.

[dluga20]

you need two tables, one for parts and one for fixtures and gauges

if a fixture or gauge can be in more than one part then you need a third 'link' table

please clarify and we can move on to what those tables need to look like

Yes a fixture or gauge can be used for multiple part numbers in some instances.

[dluga20]

Here are my relationships. I know it is probably not perfect but I am just looking to learn at this point. I think my tables are set up okay, everything is related pretty, and it will give me the functionality I am looking for. Ultimately I am looking to be able to run reports and set up forms based on the part numbers. Can anyone tell me if everything is set up well enough to be able to do this, how to go about making the forms and reports, or if anything needs to be changed in order to do so.

[CJ_London]

1. as previously advised, you need one table for fixtures and gauges (tblGuages and tblFixtures) lets call it tblItems. Add to this table another field ItemType to indicate whether it is a fixture or gauge.
2. you do not need tblFixGua
3. connect tblFixGuaPN_Link to tblItems on FixGuaID=ID
4. I notice you appear to be using a multivalue field for Models. Appreciate I do not know what this is for, but use with caution. If your list of models can change then a multivalue field is not the thing to use. Also these fields cannot be indexed so if you need to search or sort on them, this will slow your system down considerably.

[dluga20]

Okay I think I understand your point. I see one issue, however. If fixtures and gauges were in the same table with an item type field, then I would not be able to duplicate the fixture/gauge numbers field. Our fixtures and gauges are numbered like so: G00123 and F00123. I planned on keeping these fields as an indexed number type field and either format them inside the field or in the form. Any suggestions?

Also, the models field is an attachment field. It would hold 3d CAD model files for each record.

[CJ_London]

then I would not be able to duplicate the fixture/gauge numbers field.

Yes you can

a) you are linking on ID and this is the 'true' identifier
b) you can use a combination index of itemtype and F/GNumber to make sure you cannot have the same number more than once for the same itemtype - in table design on the ribbon, select indexes


Point taken about the attachment field but you might want to consider storing these drawing files in a defined folder and then simply store the filename in the db (and in a separate table if more than one drawing per fixture/guage) - will keep the db smaller and more efficient.

You also asked

what if fixtures and guages had different fields? Would i need separate tables then at that point?

It depends. if it is only a few fields, I would say no, if it is 20 or thirty fields then probably. Also depends on how the data is being used - may be easier is separate tables, maybe not. Note that an empty field does not take up any space in the db

[dluga20]

This is all very helpful information. Thank you for your help!