0

Lets say we have some configurations stored in a database for how we should acquire some data. One of these fields is an AcquisitionStrategy which denotes how we will acquire the data.

I want to add a field that corresponds only to a subset of the rows in this table that have an acquisition strategy of HTTP. Should I make a new table that maps the CrawlerType to a Table 1 ID?

Design 1
----------------------
Table 1 -> ID | AcquisitionStrategy | CrawlerType ...
           1    HTTP                  GenericWebCrawler
           2    FTPS                  NONE
           3    SFTP                  NONE

Design 2
Table 1 -> ID | AcquisitionStrategy | ...
           1    HTTP                  
           2    FTPS                  
           3    SFTP                  

Table 2 -> ID | CrawlerType
           1    GenericWebCrawler
2

I believe the better design is to make the separate table for CrawlerType and add a Foreign Key field in Table 1

Table 1 -> ID | AcquisitionStrategy | CrawlerTypeId
           1    HTTP                  1
           2    FTPS                  null
           3    SFTP                  null

Table 2 -> ID | CrawlerType
           1    GenericWebCrawler

Why I feel this is the better design than the first is that it provides obvious constraints and guarentees. In the first design you'd have to think about how you would prevent someone from inserting an AcquisitionStrategy with an invalid CrawlerType; what if there was a typo

Table 1 -> ID | AcquisitionStrategy | CrawlerType ...
           1    HTTP                  GenercWebCrawler

With the separate table and Foreign Key constraint, any attempt to insert an invalid CrawlerTypeId will fail.

insert into Table1 (id, AquisitionStrategy, CrawlerTypeId) values (1, HTTP, 17);
error - foreign key constraint 'CrawlerTypeId' violated; CrawlerTypeId 17 invalid

To me it also makes other operations easier (and probably faster), such as getting a list of unique CrawlerTypes. You can just query the Table 2

select * from Table2;

Versus needing to do something like:

select distinct CrawlerType from Table1;
2

I like Cole Ole's answer, but just to be complete you can also create that second table if you're interested in not having a null column for all non HTTP acquisitions.

Table 1 -> ID | AcquisitionStrategy
           1    HTTP
           2    FTPS
           3    SFTP
           4    HTTP

Table 2 -> ID | AcquisitionID | CrawlerTypeID
            1    1              1
            2    4              2

Table 3 -> ID | CrawlerType
           1    GenericWebCrawler
           2    FooBarWebCrawler

The downside is that it introduces a second join that needs to be made from Table 1 to Table 2 in order to lookup a given acquisition's crawler type. This could be a good idea, however, if there are other attributes that need to be stored that don't belong in either table, such as when it was crawled:

Table 2 -> ID | AcquisitionID | CrawlerTypeID | CrawledDate | CrawledServer
            1    1              1               2019-01-09    apache
            2    4              2               2019-01-10    nginx

If you need extra attributes besides just the linking IDs, this may be a better solution rather than have 3-4 null columns inside of Table 1. Doubly so if each entry in Table 1 can be crawled multiple times by either the same or different crawler types and you need to track each one.

Your Answer

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.