Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2010-08-17 : 13:18:50
|
| Hi Team,I am developing a store locator project for a franchising domain. The database will be populated with data from an existing system’s database, using a schedule job. The schedule job will run every night. Could you please tell whether the following approach is the best approach for this problem? Note: - The store locator needs data about “Active” stores only.Create a table as belowCREATE TABLE Store (Store_ID INT, Open_Dt SMALLDATETIME, Is_Active BIT) Make the schedule job logic as follows.1) Select only the active stores from the source database and store it in a table variable called “SourceTableA”. 2) SourceTableA will be LEFT JOINed with Store table. Insert those records for which the Store_ID is null in Store table. (This will insert newly created stores into our database)3) Store table will be LEFT JOINed with SourceTableA table. Update “Store” table with Is_Active = false (soft delete) for those records which are having null in Store_ID in SourceTableA table. (Delete inactivated stores from our database)4) Delete all Is_Active = false records from Store table. Please advise.ThanksLijo Cheeran Joseph |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-17 : 13:30:18
|
| If you are just going to delete the records there doesn't seem to be much sense/need to store an Active flag, just delete them.However, if you did want to keep temporal data about the Opening and Closing of stores I'd use a Start and End Data metaphor. |
 |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2010-08-17 : 13:41:29
|
| Thanks. Okay. I shall remove the Is_Active column. Is there any other major logical improvement possible in the problem? |
|
|
|
|
|
|
Database Design Question