I need some suggestions on the proper database schema for my scenario. I have an application with 1000s of users and each user has a list of 1000s of contacts. I have an API that is pulling contacts from another service into my database for each user. Their database has 20 custom fields associated with each of their contact records. It appears that each custom field is its own record in a “Contact Custom Field” table as each custom field returned has a unique id and a value. I could have 100,000s of contact records and it seems that this could get to be a large dataset rather quickly. Also, each user will need to be able to name their custom fields uniquely. However, this will be set at an account level so each contact for a user will have the same same custom fields.
My thoughts are to have a
Contact Data Type with the basic info
Custom Field Data Type with all custom fields as 1 thing.
How do I marry the custom field name with the custom field value? Can the field name just be another field in the Custom Field Data Type? If so, how do I match the custom field values from the API to the appropriate record?
Or is it best to just store each Custom Field as its own thing? Is this going to run into performance issues? This will only be searching by unique id.
Is this the best design? How would you do it? Thanks.