Composite Key

[mithiltaneja]

Hi we have a unique key "account id" which is string by data type, we were trying to make it PK but found that it is getting repeated through several markets.

So we would be making it a composite primary key of account id (string) and market code(string), but as per the best practices composite key is not recommended & also strings in keys should not be used.

Could you please help us to get a work around this to define a key using account id and market? or how we can go with string composite keys without having any issues.

[Vipul_Raghav]

You can implement any of the following solutions

1. Let the system have an autopk column and define unique index on the combination of accountid and market code.

2. Define a new schema cus:market. Define all market codes there with the PK column being an integer say marketid and then define composite key on the accountID and MarketID. marketID will be the FK column inside your main table.

Regards,
Vipul

[mithiltaneja]

Hi Raghav thanks for your reply,

1. Let the system have an autopk column and define unique index on the combination of accountid and market code.

- This solution looks easy and good for implementation but won't a combination of accountId(string) and marketCode(string) to be used as a composite key would pose issues? or it would work as fine as integer keys? we need to link other tables to this table also through PK-FK relation for which should we use combination of both indexes to identify every other record in different tables?

2. Define a new schema cus:market. Define all market codes there with the PK column being an integer say marketid and then define composite key on the accountID and MarketID. marketID will be the FK column inside your main table.

-we have also worked on this solution as a POC which dynamically tries to lookup the market table while file loading to get the primary key of market code and populate in the table along with other attributes. but here too it would be a "string-integer" relationship between accountId and marketId to define as PK-FK composite relationship.

Which should be better to use?

[Vipul_Raghav · Accepted solution]

Hi,

When you define a PK at schema level, Campaign internally creates a unique index on the involved columns. Managing btree indexes on string column is problematic or better to say not that efficient.

If you define an autopk the PK column will be a different ID column and the unique index on your composite columns will serve to maintain the data integrity. However the searches will be slow as string column as part of index don't offer that efficiency.

Option 1 is indeed a solution but certainly not the best one. Having an ID PK thru autopk will help you to perform quick joins, its just the filtering queries on marketCode that will be a bit slow.

Moving to option 2.

When you create a new Market schema, there will be two columns as per me MarketID and MarkeTCode. MarketCode will contain all unique market codes and MarketID can be the autopk column containing unique integers.

When you use this column marketID as a FK in your main table, the composite key will be on two integers accountID and marketID. there will be no string to impact the performance.

Hope this helps.

Regards,
Vipul

[mithiltaneja]

Hi Raghav thanks for the information, Option 2 does look the best solution but accountId is coming as string.

so can we use marketId(integer) of lookup table along with accountId (string).

Note: market code would be 2 char max. (IN, US etc.) but accountId can be of 10 chars, so anyhow we have to use a string for composite so which would be better? or is there any other solution to have this solution implemented with integer composite key?

Maybe we can use autopk(recipientId) and marketId somehow?