I have a table with 96 fields which will be searched on quite a lot, so based on everything i have learned over the last 3 weeks i have decided to split the table and have been able to split in to 4 tables ensuring no field is duplicated. Searching will mostly be done on the server as the data needs to be secured. Only a few records at a time will be returned.
Tables are around financial services products, named
- Table header - i have put the fields that will be searched on in this table and other fields which are common across all products. total 29 fields inc built in fields
- Life Insurance
- Home insurance
Relationships will exist between the table Product Header and one of the other tables only.
My questions are:
do i create a relationship between the two by adding a fields on the Product Header called Mortgages, Life Insurance and Home Insurance and lookup the Table but not as a list?
Can i create a form and have fields from both tables on that one form for editing purposes
Is 29 fields too many for efficient searching? Searches will most likely only be carried out on around 5 fields. Would i be better moving these to another table and create another relationship which would then involve 3 tables on a 1:1:1 basis.
Thanks for the help received so far, its been very helpful.