multiple products db structure in mssql

Question

I am looking for a better approach to add multiple products to my db. (things I have tried)

Example 1

[table Products - ProductId pk( ProductId,ItemCode,Price,ProductType)..]

[table 2 Frame - ProductId pk( ProductId,FrameType,FrameSize,Glass,Mat,Spacer)..]

[table 3 PrintRoom - ProductId pk( ProductId,Material,ImageSize,PrintType,GlossCoating)..]

[table 3 PowderCoating - ProductId pk( ProductId,ItemType,ItemSize,Coats,DesignSize,ItemColor,DesignColor)..]

Pro's and Con's

Pro = all items are in nice neat tables and easy to query separately

con = adding items can be interesting with 15 people doing data entry. if I have 2 people add an item at the same time and I am using the last call method I may grab the wrong data. (I didn't think this could happen until I got 2 matching PO numbers using (invoice.PO = (decimal)difference.TotalDays;))

Example 2

[table Products - ProductId pk( ProductId,ItemCode,Price,Attributes(loading all attributes as a hash))..]

Pro's and Con's

Pro = Easy to Query, and may even help with allowing users to add new item types.

con = a lot more error handling to make sure users don't add something they should not in a text fields, have to split and datatype everything for a grid editor to work.

Example 3

[table FrameProduct - FrameProductId pk( FrameProductId,ItemCode,Price,FrameType,FrameSize,Glass,Mat,Spacer)..]

[table 2 PrintRoomProduct - PrintRoomProductId pk( PrintRoomProductId,ItemCode,Price,Material,ImageSize,PrintType,GlossCoating)..]

[table 3 PowderCoatingProduct - PowderCoatingProductId pk( ProductId,ItemCode,Price,ItemType,ItemSize,Coats,DesignSize,ItemColor,DesignColor)..]

Pro's and Con's

Pro = easiest approach but not exactly best practice, would enable a more modular design only showing that department their orders and orders do not have multiple types in this manufacturing company at this time..?!

Con = reporting would have a lot more steps to get data from all tables and if they do decide to allow multiple types in one order it may take a major overhaul to achieve this.

• Ben Cottrell: SQL Server (and indeed most RDBMS) have several different ways to prevent the kinds of concurrency problems in example 1 - Microsoft have written at length on the subject here: docs.microsoft.com/en-us/sql/relational-databases/… (it's a non-trivial subject, without any simple one-size-fits all or "best" or "best practice" solution to concurrency problems, it really depends on requirements and the particular circumstances of the insert/update problem you're facing).
• Ben Cottrell: Although on the subject of duplicates, that should have a fairly trivial solution through the use of an appropriate constraint (e.g. UNIQUE on your PO Number column).
• Kilian Foth: Changing your database schema every time a new type of product is introduced is not sustainable. No data model which requires this can survive contact with reality.
• cal: thank you Doc Brown for the answer and thank you all for the input.. I am a code guy not a db guy .. I inherited this project from a guy that actually used the PO code I posted and with his logic there was 10ドルk of unbilled orders per year.. I just don't want to end up in the same boat. they are currently running on my first build for 2 years now without issue but this is the first time I have tried to implement any type of Supertype subtype structure.. so again any info is appreciated.. thanks again.
• cal: @KilianFoth you have a very good reputation here and I am sure you are very knowledgeable and I am not sure if your comment was to be serious or sarcastic. in either case that is not always true. this is a large scale manufacturing company not much changes day to day. and the products they do make don't all travel the same path.. ie. frames will go to assembly where employee time will be logged per piece then QC then Billing. where engraving goes strait to shipping. each with there own form views. so if they do add a new product they will want views for the new product created

Answer 1

You approach this from the wrong angle - there is no need to denormalize your data model for making concurrent access work on an RDBMS like MSSQL.

Theoretically, concurrent access could be a problem in the normalized approach #1 (and indeed, if one implements it wrong, this becomes a real problem). But transactional databases already provide all the means to solve the problems of concurrency for you:

• properly placed transactions (COMMIT and ROLLBACK statements) make sure that manipulations of multiple records which form an entity will be kept atomic - even with 100 ore more connected users, anyone will only see "all-or-nothing" changes by all others.

• auto key generation will make sure each new inserted record into a table will get a new ID even when concurrent inserts happen, and @@IDENTITY and SCOPE_IDENTITY are the tools for making sure each process/connection will get access only to its own "latest" ids

If, however, you start to fiddle around by implementing your own key generation mechanism based on time stamps, sooner or later you will shoot yourself in the foot (as it seems you have already learned the hard way). Similar problems will happen if you don't place transactional brackets correctly.

Let me add a note on this:

if I have 2 people add an item at the same time and I am using the last call method I may grab the wrong data.

This was a pretty terse description, and I can only guess what the LastCall method is intended to do in your code base. But if you have a requirement for implementing a method which get a user the latest record they added by themselves (in a multi-user environment), the entity records require a column CreatedBy field, so one can filter for "own" records and distinguish them from other records. And this is true regardless of modeling approach #1, #2 or #3.

• candied_orange: One thing I've learned about composite keys. They can be very handy when importing a batch of data after you've proven the composite is unique. When you later start automatically adding new data with the old, stop believing you've proven that the composite is unique. At this point you haven't. Learn to love automatically generated primary keys that have no meaning other than to be a key.