Need help organizing tables

[KentKHI]

Hey Folks,

I am looking for advice on how to store (phase/subphase/item) information I have about 35 phases; each phase has between 2 and 10 subphases; each subphase has anywhere from 3 to 100's of items.

Do I create a table with all the phases and thier ID's, then a table for each phase listing the subphases, and a table for each subphase listing the items? This seems like a lot of tables and table names. Is there a better way to organize it as to not get too much duplicate info?

I know this is a (simple-minded) newbie question, but I want to start on the right foot with proper organization.

Thanks,

Kent

 

[Norie]

I think you want 5 tables phase, subphase and subpahse to hold the data and 3 to show the relationships.

Something like this (PK- Primary key, FK - Foreign Key)

Tables

Phases

PhaseID AutoNumber PK
PhaseName Text

SubPhases

SubPhaseID AutoNumber PK
SubPhaseName Text

Items

ItemID AutoNumber PK
ItemName Text

HasSubPhase

HasSubPhaseID AutoNumber PK
PhaseID Number FK from Phases table
SubPhaseID Number FK from SubPhases table

HasItem

HasItemID AutoNumber PK
SubPhaseID Number FK from SubPhases table
ItemID Number FK from Items table

Relationships

Phases to HasSubPhases - 1 to many based on PhaseID
SubPhases to HasSubPhases - 1 to many nased on SubPhaseID

SubPhases to HasItems - 1 to many based on SubPhaseID
Items to HasITems - 1 to many based on ItemID

 

[KentKHI]

Thanks so much for the quick reply! I think I follow what you are saying, but I have a question on the Auto Number. My phases and subphases have ID numbers like 100.110 and 40.200. Do I need an Autonumber as well, is this a faster method for the computer? Or will it take up more memory?

Thanks Again,

Kent

 

[Norie]

Kent

Keep your ID numbers as a field - you'll be able to use them in lookups for fields that are foreign keys.

A primary key is just sort of arbitrary identifier that is used for relationships rather than to represent actual data.

 

[KentKHI]

Hey Norie!

Just another question. I don't understand how to set a foriegn key. My train of thought is that in the "Has" tables I will create a field with the data type as a lookup value off of the other table. Am I on the right track, or is a Foreign Key something I set in another way?

I am assuming I should assign subphases to phases and items to subphases by a field in each of these tables that shows which phase or subphase it is assigned to. I match it with the Phase ID which is the simplest string.

Am I lost or What?

Sorry, I am going to get a book or two on this soon so I don't have to bug people (as much )

Thanks,

Kent

 

[Norie]

The data type of the foreign key will be Number to match the AutoNumber.

I'm not sure what you mean by simplest string for PhaseID - you should use an AutoNumber for the PhaseID.

When I said you could keep the existing ID's I meant in another field.