-1

I would like to ask if there is any powerful method we can use to combine the Id's to a single field for faster search and retrieval.

My case is like this, I have a table that stores ID's of companies.


ID | CompanyName


1 | Company A

2 | Company B

3 | Company C

I have another table where there is data that need to be processed by these companies. so i am looking for a better method to find the data that is not processed by a specific company.


Data | CompaniesProcessed


A | 1,2

B | 3,2

C | 3

one easy way i can think is store the company ID's as a separated value and search for it. The data set to search can be huge, so, Is there any other way that will be faster for search rather than the string operation?


EDIT: I need to read through data and check if it is already processed for a specific company. so if this is linked table i have to do a read operation of data and search the linked table to know if it is processed. However, if it is in a single table, i can just filter out those records that are processed while i read.

New contributor
Hybridzz is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 1
    Is there any reason why you don't want to use a link table? Also, please define "huge". – Doc Brown Jan 10 at 6:39
  • yea, there is. i will edit the question. – Hybridzz Jan 10 at 6:44
  • 2
    Are you aware that, when using a link table with proper indexes, the "filtering" is done by the database in an efficient manner? For example, when you want to check if data record A (with ID=10) is already processed for company B (with ID=2), you use something like SELECT ... FROM LinkTable WHERE DataID=10 AND CompanyID=2 and check if the resultset is empty or not. This requires a combined index on (DataID,CompanyID) to be efficient. – Doc Brown Jan 10 at 7:02
  • so linked table will be most efficient way here? I just wanted to know if we have some other option available or not. – Hybridzz Jan 10 at 8:32
  • 1
    There is no "most efficient way" in general. You need to know the number and type of expected queries, what "huge data set" means, and your performance requirements. And you should also consider that "performance" is not the only non-functional requirement you have to take into account, extendibility or making things consistent may be also important. Then you can try out what works best and measure. For a purely sequential batch processing, storing a list of IDs in just one column, as you proposed, can turn out to be the fastest way for exactly that process, in exactly this situation, ... – Doc Brown Jan 10 at 11:51
4

This is a many-to-many relationship and you can model it with an intermediate table, which contains the relationships between Companies and Data.

So, you will have 3 tables:


ID | CompanyName

1 | Company A

2 | Company B

3 | Company C


ID | CompanyData

10 | A

20 | B

30 | C


DataID | CompanyID

10 | 1

10 | 2

20 | 2

20 | 3

30 | 3

Now you can query very efficiently what companies use Data A, or what data company 2 uses and it will be a lot faster than searching ids within a string.


EDIT

As Doc Brown correctly says in a comment, in order to make this approach work optimally, specially for queries involving non Primary Key columns, proper indexing needs to be in place.

  • yea, this is true. But I remember somewhere i saw using a single column with some arithmetical operation instead of string operation to identify the ID's involved. – Hybridzz Jan 10 at 6:37
  • In the question, i just gave a simple example of my actual problem. In my actual case, I need to read through data and check if it is already processed for a specific company. so if this is separate tables i have to do a read operation + a search operation. However, if it is in a single table, i can just filter out those records that are processed while i read. – Hybridzz Jan 10 at 6:41
  • Well, the many-to-many table is a lot more powerful than what I wrote in my answer. You can add more columns. For example, "IsProcessed", or, instead of removing an association, you can add an AssociationEnabled, so you can switch it on and off, and so on. So, searching companies with Data A, which are Enabled and Not Processed is still very efficient. – Francesc Castells Jan 10 at 7:49
  • ok, agreed. I will be following this way. but is there no other efficient way of using a single column? – Hybridzz Jan 10 at 8:29
  • @DocBrown true. Thanks for the comment. – Francesc Castells Jan 10 at 22:35

Your Answer

Hybridzz is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.