hi,
i am trying to create a data model (sql server 2008) for the data transfer adapter factory i am building. some examples of an adapter would be FTPS, email, and web service (WS). right now this is what i have as far as DB design (see attached image):
- Adapter table - each row is a different type of adapter
- AdapterProperty table - properties of an adapter, e.g., Url, username, password
- AdapterPropertyValue table - values for each of the properties, along with the client ID (IntegrationServiceProvider_BusinessUnitID) for the fact that multiple clients may be using the same adapter type
i want to modify this schema so that AdapterProperty only contains common properties to all adapter types, e.g., Url and username. this will allow me to add extended property tables for each adapter type that will contain properties unique to that adapter type. the reason i am doing this is so that my schema is as flexible as possible, and the AdapterProperty table won't be cluttered with duplicate values, e.g., Url for both FTPS and WS adapters.
keep in mind, i don't mean extended properties in a table via sql server management studio, but literally a table that contains extended properties unique to an adapter type as opposed to the AdapaterProperty table that contains common properties. can someone help me?