Database Search Tables

Question

I am currently trying to improve the design of an old db, and I have the following situation.

I currently have a SalesLead table in which we store a LeadSource.

Create Table SalesLead( .... LeadSource varchar(20) .... ) 

Data sources are useful in a table.

 Create Table LeadSource ( LeadSourceId int, /*the PK*/ LeadSource varchar(20) ) 

And so I just want to create a foreign key from one to another and delete the irregular column.

All standard things, hopefully.

Here is my problem. I can't seem to get away from the question that instead of writing

  SELECT * FROM SalesLead Where LeadSource = 'foo' 

Which is very clear, I now have to write

 SELECT * FROM SalesLead where FK_LeadSourceID = 1 

or

 SELECT * FROM SalesLead INNER JOIN LeadSource ON SalesLead.FK_LeadSourceID = LeadSource.LeadSourceId where LeadSource.LeadSource = "foo" 

What happens if we ever change the contents of the LeadSource field.

In my application, when ever I want to change the value of SalesLead LeadSource, I do not want to update from 1 to 2 (for example), since I do not want developers to have to remember these magic numbers . Ides are arbitrary and must be preserved.

How to remove or deny dependency on them in the application code?

Change Languages ​​whose solution will support

• .NET 2.0 + 3 (what is asp.net, vb.net and C # for)
• vba (access)
• db (MSSQL 2000)

Change 2.0 . The connection is fine, so "foo" can change on request to "foobar", and I don't want to iterate over requests.

Answer 1

If you want to de-normalize the table, simply add the LeadSource (Varchar) column to the SalesLead table instead of using the FK or identifier.

On the other hand, if your language supports ENUM structures, magic numbers should be safely stored in an enumeration so that you can:

 SELECT * FROM SALESLEAD WHERE LeadSouce = (int) EnmLeadSource.Foo; //pseudocode 

And your code will have

 public enum EnmLeadSource { Foo = 1, Bar = 2 } 

It is normal to remove excessive normalization if it causes more problems than what it fixes. However, keep in mind that if you use the VARCHAR field (as opposed to the Magic Number), you must maintain consistency, and it can be difficult to localize later if you need multiple languages ​​or cultures.

The best approach after normalization seems to be to use the Enum structure. It keeps the code clean and you can always pass enumerations through methods and functions. (I assume .NET is here, but also in other languages)

Refresh . Since you are using .NET, the database back-end is "not important" if you create a query through code. Imagine this function:

 public void GiveMeSalesLeadGiven( EnmLeadSource thisLeadSource ) { // Construct your string using the value of thisLeadSource } 

The table will have a LeadSource (INT) column. But the fact that it has 1,2 or N will not matter to you. If later you need to change foo to foobar, this may mean that:

1) All "number 1" must be the number "2". You will need to update the table. 2) Or you need Foo to be number 2 and number 1 of the bar now. You just modify Enum (but make sure the table values ​​remain consistent).

Enum is a very useful structure when used properly.

Hope this helps.

Answer 2

Did you think that you weren’t using an artificial key for the LeadSource table? You can then use LeadSource as an FK in SalesLead , which simplifies your queries, while preserving the benefits of using a canonical set of values ​​(rows in LeadSource ).

Answer 3

Have you considered an updated presentation? Depending on your database server and the integrity of your database project, you will be able to create a view that, when its values ​​change, will in turn update the composite tables.

Answer 4

I really don't see your problem behind the connection.

Naturally, requesting directly with FK_LeadSourceID is incorrect, but using JOIN seems to be the right way, since I mask the changing identifiers completely fine. If, for example, "foo" becomes 3 on the same day (and you update the foreign key field), the last request you specify will still work exactly the same.

If you want to make changes to the schema without changing the current requests in the application, then the view that includes this connection is the way to go.

Or, if you are afraid that the syntax of the connection is unintuitive, there is always a subheading ...

 SELECT * FROM SalesLead where FK_LeadSourceID = (SELECT LeadSourceID from LeadSource WHERE LeadSource = 'foo') 

but don't forget to keep the index on LeadSource.LeadSource - at least if you have a lot of them in the table.

Answer 5

If you are "improving design" by introducing new relationships / tables, you will surely have a need for different entities. If so, you need to deal with their semantics.

In the previous solution, you could simply update the LeadSource name to whatever you want in the corresponding SalesLead line. If you update the name in your new structure, you will do this for all SalesLead lines.

There is no way around this different semantics. You just need to do this. To simplify querying the tables, you can use the views, as already suggested, but I would expect them mainly for reporting or backward compatibility purposes, provided that they will not be updated, because everyone updating this view will not know about the changed semantics.

If you don't like trying to connect SELECT * FROM SalesLead, where LeadSourceId IN (SELECT Id FROM LeadSource WHERE LeadSource = 'foo')

Answer 6

In a typical application, the user will be presented with a list of source sources (returned by querying the LeadSource table), and a subsequent SalesLead query will be dynamically created by the application based on the user's choice.

Your application has several “well-known” source sources for which you need to write specific queries. If so, add a third (unique) field to the LeadSource table, which includes an invariant "name", which you can use as the basis for your application requests.

This transfers the burden of magic from the magic number generated by the database (which can vary from installation to installation) to the system name of magic (which is fixed by design).

Answer 7

There is a false dichotomy here.

 SELECT * FROM SalesLead INNER JOIN LeadSource ON SalesLead.FK_LeadSourceID = LeadSource.LeadSourceId where LeadSource.LeadSource = "foo" 

does not break more than the original

 SELECT * FROM SalesLead Where LeadSource = 'foo' 

when foo changes to foobar . Also, if you use parameterized queries (and you really should be), you don't need to change anything if foo changes to foobar .