Help designing a table

[webfactoryuk]

Hi,

Im creating a warehouse racking inspection reporting system (mouthful I know!) and I need to have a combo box that is dependant on a previous combo box.

Looked through some guides and I think I can work that part out but its the table Im not sure about!

Each racking component will have a list of defect and rather than have a massive list I would like to filter it based on the component selected in the combo box above.

Do I need to use multiple tables or can I have the columns as

ID - Component - Component Defect


The reason I ask is one component could have the same defect as others but then it will have ones original to it aswell.



Aslong as the component field is different, does it matter if some of the text in the defect column is repeated?

For instance

ID - Comp - Defect

1 - Beam - Bent
2 - Beam - Loose
3 - Beam - Buckled

4 - Bolt - Bent
5 - Bolt - Missing


So ID 2 and 4 have differents comps but the same defect.

Will it still work for doing something like this

http://www.datapigtechnologies.com/f...combobox2.html


Thanks in advance and sorry for being a noob!!

[orange]

The tutorial at datapig is excellent. The topic re comboboxes is Cascading Combos, and samples/explanations can be found by Googling that phrase.

What have you got for tables for your database? That may be the key to some of what you're doing/asking.

Perhaps you could describe your environment a little more to help us picture what a
warehouse racking inspection system is.

If it is Components and Defects, then maybe there are 3 tables involved.

Components and Defects, and a 3rd table

ComponentHasDefect which has the ComponentId and the DefectId
and could have Multiple Defects per Component

ComponentHasDefect would be a Junction table to resolve a
many-to-many relationship between Components and defects.

Anyway, just a few thoughts that are intended to be helpful and constructive.

[jzwp11]

oops! I see orange responded first...

The reason I ask is one component could have the same defect as others but then it will have ones original to it aswell.

The above statement implies that a component can have many defects which describes a one-to-many relationship which requires a separate table. Since a defect can apply to more than one component you have another one-to-many relationship. When you have 2 one-to-many relationships between the same two tables you need a junction table.

tblComponents (beam, bolt etc.)
-pkComponentID primary key, autonumber
-txtComponent

tblDefects (a table to hold all possible defects: Bent, loose, buckled etc.)
-pkDefectID primary key, autonumber
-txtDefectName

tblComponentDefects
-pkCompDefectID primary key autonumber
-fkComponentID foreign key to tblComponents
-fkDefectID foreign key to tblDefects