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 |
|
zavier
Yak Posting Veteran
50 Posts |
 Posted - 2003-01-14 : 20:52:16
|
| Hello AllIf someone more experienced than I could help me with this one I would really appreciate it. I have a department that wants to place items on sale on their part of the company website (which is good because I think this is gonna be a mess) and only about 10% of the items are actually ours. We actually recieve a file of data from a partner company that provides the other 90% of the products which we can then import into our database and they can then be viewed as part of the normal product catalog on our website. The partner company sends us a weekly file of their full catalog with price changes, new additions, deletions and edits to their inventory. So each week we must delete the records from the database that are theirs and make sure not to delete ours and then reimport the new text file. The problem is that I have been asked to display more information about our products than are available for the products provided by the partner company when a consumer chooses to view more detail.Currently I keep all the records together no matter wether they are ours or the partner companies. I keep the extra information that is only available for our products in a seperate table and preform a join on the two tables only when I need to show the detailed information. I added a field to the main table that differentiates wether the product is ours or the partners.My question is: Is this a good idea? I'm afraid we might end up deleting products in our inventory at one point or something silly like that. I would love to somehow safeguard our products from ever being in the same table that is getting 18,000 records deleted from it every week. Can anyone suggest a better way. I thought of keeping two seperate tables one for our in house products and one for theirs and preform a join but that would cost us some preformance as I was instructed to always allow the consumer to view the products as if they were all ours so we cannot show any seperation. This means that from now on our nice and fast database must preform a join on every query and this database is about 20,000 records with quite a few fields searchable so it takes quite a hit by employing a join all the time.Anyway, if anyone can suggest a better way to handle this, I would love to hear it. ThanksZavier |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2003-01-15 : 03:06:02
|
| Well, Zavier, my first suggestion is be sure you're running a backup before you delete all those records. Then, assuming that you have, or are in the process of, automating this delete/import process, you shouldn't be in danger of accidentally deleting your own records, as long as you're using that Ours/Theirs flag in the DELETE command. And as long as the flag is set properly on your data.As for the additional information, is this just a longer description field? Or are there several other fields? Either way, it sounds like you're doing a 1-to-1 relationship here, which I normally try to avoid, but given the small percentage of records that will be in the related table, it might be the right choice in your case. But if you wanted to keep all the data in just one table, then it seems you could easily make sure that the data is not being inserted during the import process, and/or you can code your web page to not display those fields based on your Ours/Theirs flag. By the way, you might want to expand that from just an Ours/Theirs flag to an actual code field indicating the source so that you could later add more source companies and not have to worry about it.And despite all that rambling, I would think that SQL Server is pretty darn speedy with only 20,000 records no matter what you do.------------------------------------------------------The more you know, the more you know you don't know. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-01-15 : 08:04:36
|
Zavier, my advice would be to take a more scientific approach here. I didn't hear you talk at all about normalization, logical design or data integrity. Normalize your data tier and protect the integrity of your data throught the relational model.I'm guessing your JOIN = poor performance issues are purely a misguided assumption. With your data sets as described, I don't believe denormalization for performance exists.quote:
I was instructed to always allow the consumer to view the products as if they were all ours so we cannot show any seperation
quote:
"The DBA maps the logical model to a physical model, then makes the logical model accessible to application developers, who should not be exposed to the physical details."-Fabian Pascal
Your physical implementation should be of no concern to anyone else in the organization.Jay White{0} |
|
|
|
zavier
Yak Posting Veteran
50 Posts |
Posted - 2003-01-20 : 09:21:03
|
| Thanks very much for your suggestions. I will think it through and post my final resolution. |
|
|
|
jns
Starting Member
19 Posts |
Posted - 2003-01-20 : 09:42:01
|
| We've run into this issue on several occasions. I wouldn't entirely suggest an Ours/Theirs flag. What happens if in the next few months you have another company that wants to sell their items? I would add a INT column with a relationship to another table's (new table) that specifies what company the items belong. I'd assume the first record (id 1) would be your company and so on. That way you can easily add new companies and their items as well as easily identify which ones need to be deleted when the time comes. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-20 : 15:58:41
|
| (at the risk of getting in trouble I'll offer some advice...)I would not delete records when you get the files, but rather add new or changed ones and mark deleted ones as "inactive" or something along those lines with an added field. If you have other transactions linking to this table and you delete records, you could be in big trouble. Also, you will then have a history of all items you ever had for sale from all of the files. Simply filter out "inactive" items as needed or create a view that does this. But by not deleting the records it will be nice to know they will always be there for reporting and data integrity.Also, I agree with jns -- think about the future and instead use a "company" field or something along those lines.- Jeff |
|
|
|
|
|
|
|
|
Database Design For Constantly Updated DB