gnichols

in a general fashion, can anyone from D-Tools explain the basic structure of the SI5 Product db?

What I'd like to understand is, which field is considered the key or unique field for each record? Is it the ModelName field, or is there another (hidden?) field that is the record number?

Depending what the answer is, I'll probably have a followup question...

Thanks.

./griggs

SenoS

There are three fields that uniquely identify records in the SI database: Category, Manufacturer, and Model.

gnichols

Originally Posted by SenoS

There are three fields that uniquely identify records in the SI database: Category, Manufacturer, and Model.

Thank you.

Without knowing the entire database structure (tables, etc) and the reasoning for the present method, I don't know if I can make an entirely informed suggestion, so forgive me. However, wouldn't it be a good idea to have a "Unique Record Number" field or something like that so that when products are exported and then imported back in with changes, those changes are picked up and not just added as new items into the database?

(I just went thru this the other day, and in the process of "correcting" items model names, the corrected items were then added as new items and the old items remained, without being updated - for obvious reasons.)

With a unique record number, whenever an item is added to the database, the record number is automatically increased by one. This would allow for editing of the data outside of the database (it's much easier to do large scale corrections in Excel) and then reimport and updating of the changed/added records even if the Category, Manufacturer, or Model are changed.

Am I making sense?

Thanks.

./griggs

SenoS

Yes, you are making sense. However, that is not how our database was designed. There is a primary key for each record in the database but we do not export that info to Excel. When you import an spreadsheet to SI the Category, Manufacturer, and Model fields are what are checked. If you change any of these three fields, it will create a new record in the database and the old record should be deleted to avoid confusion. Nothing worse than adding a product with outdated pricing because you have two or more instances of that product in your DB.

gnichols

Originally Posted by SenoS

Yes, you are making sense. However, that is not how our database was designed. There is a primary key for each record in the database but we do not export that info to Excel.

Maybe that shouldn't be a default field for exporting, but it would be a great option to have. Conversely, being able to include that field for imports would be a big time saver as well. Logic could be, "If N-Field is included in import, then match on that, ELSE match on Category, Manufacturer, and Model."

Originally Posted by SenoS

When you import an spreadsheet to SI the Category, Manufacturer, and Model fields are what are checked. If you change any of these three fields, it will create a new record in the database and the old record should be deleted to avoid confusion. Nothing worse than adding a product with outdated pricing because you have two or more instances of that product in your DB.

I think you just supported my idea with that statement.


As an experienced db admin, there are days I get very frustrated with the lack of more advanced admin capabilities in SI5. (Such as what we are talking about above.) I understand and accept the general reasoning for not having these features readily available to the lay-person (let's keep the less experienced away from things that may hurt them.), but at the same time you're also removing a lot of the real power of using a database to begin with... argh.

I'm not quite sure how I should go about making this a "feature" request... Any ideas? Would you mind submitting this thread?

Thanks for your help.

./griggs

SenoS

No problem, I will submit this as a request to our Development team. SI 6 is going to be re-written from the ground up so our data structure is bound to change. Being an experienced db admin, you may prefer to go directly into the MasterTable.mdb and edit there or export to Excel from there. Our databases are password protected but you can contact http://www.d-tools.com/support/submit-an-issue.html for a link to our NDA (non-disclosure agreement) and once you sign and fax it in, they will send the passwords. As always, make a backup of the db before doing anything...