access relations

[fran7]

Hi, I just wonder if anyone can advise about this.
I have an access database with two tables. One logs in a user and in the other the user can modify many fields. These are linked by userid with a relation one to one. The userid in the first table is autonumeric and in the next table is number. The second has a principle key called profileid too. I want to break up the second into smaller tables as its getting too big.

The question is do I link from the second table to the other tables I will create by userid and with a relation one to one, or by profile id as thats the principle key?
Any advise would be greatly appreciated.
Thanks
Richard

[ChipR]

What about your second table is too big, and how do you plan to break it up?

[NeoPa]

My advice would be not to break it up (from what you've said so far).

Maybe if you can explain your problem (that you think splitting the table will help with), we can look at more practical alternatives with you.

Please check out Normalisation and Table structures for a better understanding of the sort of trouble you could be letting yourself in for.

[fran7]

Thanks for both replies and also the normalisation link I will look into. Well I have read before that a table shouldnt have more than 30 fields and the one I thought I should split might get up to 80. As there will be users updating and deleting, although I dont envisage more than 400 users, I thought it might be too much to handle, although I might be completely wrong.
Thanks again fo any advice.
Richard

[ChipR]

Are these 80 fields somehow related? If you want to give some more information about what the table and fields are, we can probably help with the design. Only if you need it after reading about normalization, of course.

[fran7]

Thanks,
I read the normalisation guide.
All my fields relate to the individual user. Image for example they are 80 fields about the users personal preferences. I suppose in that case they should stay where they are, it just seems a large amount of fields. I was imagining that as the user might on any individual occation just want to update certain groups of their details that maybe they should be broken down. For example, they have their favourite links area in their control panel so I was thinking maybe the links could be in a sub table.
Thanks
Richard

[NeoPa]

What about different forms to handle the various different sections of the record?

This could be extended to pages (tabs) on a main form, either with or without subforms in them.

[fran7]

Thanks for the reply. I do actually have different forms for the different sections. I thought i also should break up the database into smaller tables, but i suppose as I do have different forms its not necessary. Does that mean that a table with so many fields is ok if the form query and update are for only a few of the fields.
Thanks for all your help and getting me to understand things better
Richard

[NeoPa]

You're welcome Richard :)

As for "Is it ok?", I would think it is. I don't have a great deal of experience with forms, but I see no problem with it.

I do actually have a few tables with a bunch more fields than that in them, and haven't noticed problems with them, although I only ever use a subset of them (The data originates from our main system so the tables reflect what is available there).

[ChipR]

It does make more sense to break your groups of fields into separate tables. Consider the fact that you can add more Favorite Links if you do that, while if you have it in the main table you are talking about allocating a static number of fields to hold them. Your table should not contain lists this way, they should be in another table.

[NeoPa]

@fran7
If this means that you are considering maintaining a list within your record, then that is not a good idea (as Chip says). I was under the impression you were talking about One-to-One linked tables.

The separate table you need for this should be linked as One-to-Many.

[fran7]

Thanks guys, really helpful all. I think you are right, best to split for more flexability later (adding new fields etc) and one to many is what I needed to know.
Thanks
Richard

[NeoPa]

I'm very pleased to hear it Richard.

And well spotted Chip. I'd missed that nuance.