how to make fully related database? having trouble

[Christopher Fro]

Hey guys, i am quite new to this database thing, instructions on the net i find confusing, i was wondering if someone could point me in the right direction

heres what im trying to do:

Create a fully related database that sorts data into seperate tables as records are created in the Primary Table and further info added to the secondary Table via the relationship

what i have:
Primary Table: Items
Objects in table:
ItemName(primary Key):Text
ItemType:Text

Secondary (one of many in plan): Weapons
Objects:
ItemName(Primary Key):Text
Volume:Text
PackedVolume:Text
WepType:Text
ChargeSize:Text
ItemTyppe (as you can see, a few are related to the primary table, relationship between the primary and secondary isnt the problem)

Thrimary Table (related to secondary but not related to primary):HybridWeps
Objects:
ItemName(NO primary key):Text
ChargeSize:Text
WepType:Text (relationship between this table and the secondary was fine until the relationship somehow broke causing the whole database to bug up now i cant get secondary table to see the third like it was before it bugged on me (bug as in big white square when accessing the relationship + button instead of the related records) now not even a + button)

checked, double checked and triple checked for mispellings and records that dont match

what the hell :S

The relationships:
Primary: ItemName > Secondary: ItemName (enforced ref integ

Secondary: ItemName > Third:ItemName (basic relationship)

PS i have BASIC knowledge of Access but havent used it in ages (*since school*) so i forgot most of it, maybe im just trying to do this the completely wrong way so if someone could give me a step by step on how to fix this, i would greatly appreciate it :D

editz: the problem is its reversed: the +s appear in the third table but not the second table, however, the related records are blank...

strange.....ive just closed it and opened it 4 times now its working, what the hell, ok how do i avoid this?

although it is now workign, the fields in the third table arent updating from the second (second is updating from third)

cant make a Enforced Ref integ relationship for obvious reasons....not all of the data from the Second table needs to be in the third, what do i do :S

also when attemping to make a multi link relationship i keep getting "no unique key found for the primary table referenced" so i cant do that either.....but there IS a Unique Key in the Table that im trying to link from...i dont get it what am i doing wrong?

[mshmyob]

Nothing works as you want because your design is not normalized. Read up here on normalization.

In the mean time please explain what each table represents and each field and then give some sample data to look over.

cheers,

[Christopher Fro]

heres the database:

[attached]

done all i can now i am getting "this phrase is too complex....."

see what u can do :D

none of the relationships are working now maybe u can see what imdoing wrong, the data should speak for itself what it all stands for

thanks again

btw this is based on a game, FYI: the game is called: EVE Online - Incursion

later on i hope to use vb2007 to make an app which will let people modify the data without the need to have access 2007 installed

[mshmyob]

Thanks Christopher,

I will look at it tonight.

cheers,

[mshmyob]

See attachment.

I looked online about your game and the info you are trying to save.

I think you are going to run into a problem if you want to save all sub categories.

What you are trying to work with is hierarchial data. The relational model does not handle hierarchial data very well. Remember the tables are just really flat lists that are interconnected with relations.

Since your subcategories are not consistent between parent and child you have no idea where to stop.

The two methods for handling hierarchial data in a RDMS is to use either the Adjacency List Model or the Nested List Model.

This is much more advanced than simple normalization but if you wish to go that route to really expand your database and make it like the databases online I would gladly help. The databases online are probably using an XML base data structure which works well for hiearchial data structures.

But like I said if you are willing to put some effort into it then I will help you with an Access version that supports hierarchial data.

cheers,

[Christopher Fro]

thanks for your reply, ive had a look at the database attached

i'll have a proper look at it later to see how exactly it works

your answer was a big help tx again :D

[mshmyob]

Just want to say that I did nothing concerning the hierarchial structure of the database. I really just fixed up your PK's and FK's.

I actually may expand on this database and create a fully functioning database that shows how to handle an unbalanced hierarchy like you will end up with and make an Insight article. I think it would be an interesting topic for the community.

cheers,