October 13, 2011 at 12:01 pm
Hey all,
This is my first post but I'm hoping to get some insight from those that have more sql DBA experience than which should be easy 😉
I'm creating new database design to replace an existing ones. We have duplicate fields spread across multiple databases, a result of the we need it now development.
I'm trying to combine similar attributes to one table and use appropiate foreign key relationship. The part that has me stumped on is the inventory side our process. We have parts that we created and are placed into inventory. In our existing design, we basically have nearly identical tables to represent the different stages/processes of inventory. My first thought was to combine all inventory tables together into one and use one column as discriminator. However, I'm worried about read/write performance over time. Also, since we output various hourly/daily inventory results to an offsite system, I'm not sure how and/or if I should split the table up.
Thanks in advance for any suggestions,
Ryan
October 13, 2011 at 3:26 pm
If you think keeping things partitioned is a good idea for performance reasons, you should look into table partitioning rather than using multiple tables. Having said that, making sure your table has good indexes (particularly a good clustered index) is probably the greater concern in this context.
I don't understand how the communication with the offsite system is having an impact on your schema choice. Can you elaborate?
October 13, 2011 at 3:51 pm
Thanks allmhuran for the reply.
I've looked at table partitioning but not exactly sure how to implement it because all the examples I've seen is by range, not a discriminate value.
Some parts go through multiple processes that must be reported. My initial thought was to update an "inventory category" column because the part id should be unique. This would ease some of my concerns for data integrity and performance. However, sometimes, the parts (not many) may be sent through two process in one day which could throw off daily results. That had me thinking of maybe making the inventory category and part id be primary key. But that would mean I would have 3 rows per part id. I'm just trying to find the best way of how to design this table. I've included my schema below.
CREATE TABLE [Production].[Inventory] (
[SerialId] VARCHAR (18) NOT NULL,
[Status]VARCHAR (2) NULL,
[InventoryCategoryId] CHAR (3) NOT NULL,
[PartNumber] VARCHAR (15) NULL,
[ModuleCaseId] VARCHAR (18) NULL,
[ContainerId] VARCHAR (10) NULL,
[LocationId] VARCHAR (15) NULL,
[Tracking] XML(CONTENT [Production].[InventoryTrackingSchemaCollection]) NULL,
[rowguid] UNIQUEIDENTIFIER ROWGUIDCOL NULL,
[ModifiedDate] DATETIME NULL
) ON [Production];
October 13, 2011 at 4:09 pm
ryanchill (10/13/2011)
Thanks allmhuran for the reply.I've looked at table partitioning but not exactly sure how to implement it because all the examples I've seen is by range, not a discriminate value.
Use the descriminate values as your range(s). You're basically just splitting the data off for your parameters.
Some parts go through multiple processes that must be reported. My initial thought was to update an "inventory category" column because the part id should be unique. This would ease some of my concerns for data integrity and performance. However, sometimes, the parts (not many) may be sent through two process in one day which could throw off daily results.
What I'm thinking you'll want is a parts table with necessary descriptors, an inventory table for how many are in each process, and a logging table describing what processes moved what inventory from each of the 'bins'.
This way you have real time access to the bins, and a logging table that can show you what moved things between positions.
Something like:
Create table Part
PartID, PartName, Size, Weight, color.
Create Table PartInventory
PartID, PartProcess, Amount
Create Table PartLogging
PartID, ProcessCompleteDateTime, Amount, FromPartProcess, ToPartProcess
Create Trigger PartLoggingInsert
-- Code to move inventory in PartInventory Around
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 14, 2011 at 12:12 am
That looks right to me as well from what I've gathered.
You've identified parts and processes as nouns in your description which, as a general rule, makes them likely candidates for entities in your logical model, and then (with tweaking) tables in your schema.
A logging system to represent the movement, with timestamps, of parts through processes then becomes a reasonably clear deduction. Based on the reasoning in the previous paragraph, it's also a likely table candidate.
Edit: Although.... I wouldn't have a trigger on the logging table drive the changes to the inventory. The log is recording (dependent on) changes to inventory, not the other way around. To put it another way, you could make this system without a logging table if you didn't care about logging, but you couldn't make it without your inventory table.
October 14, 2011 at 1:45 am
Use the descriminate values as your range(s).
Just to explain Kraig's post. It should be some kind of enumerator or Key-Value pair.
Key - Value
10 - Table_Purpose1
20 - Table_Purpose2
.
.
.
N - Table_PurposeN
Now you can follow, partitioning examples by ranges.
October 14, 2011 at 6:11 am
Thanks everyone for you help. I really wanted to make sure I was headed in the right direction because our existing database(s) design is experiencing timeouts, deadlocks, and latency here and there and I want to avoid this issues with the new schema.
Each part is barcoded (the part id) and the number of barcodes per day in the inventory process (die casting/machining/assembly finished goods) is what gets reported to our regional system.
So here's what I'm thinking...
CREATE PARTITION FUNCTION InventoryFinishedGoods (char(3))
AS RANGE LEFT FOR VALUES ('DFG', 'MFG', 'AFG');
GO
CREATE PARTITION SCHEME InventoryFinishedGoodsScheme
AS PARTITION InventoryFinishedGoods
TO (InventoryFileGroup)
GO
CREATE TABLE [Production].[Inventory] (
[SerialId] VARCHAR (18) NOT NULL,
[InventoryCategoryId] CHAR (3) NOT NULL,
[PartNumber] VARCHAR (15) NULL,
[ModuleCase] VARCHAR (18) NULL,
[Container] VARCHAR (10) NULL,
[Location] VARCHAR (15) NULL,
[ModifiedDate] DATETIME NULL
) ON [InventoryProcessScheme (InventoryCategoryId)];
GO
CREATE TRIGGER [Production].[iuInventory]
ON [Production].[Inventory]
AFTER INSERT, UPDATE AS
/*
insert changes into log tracking table
*/
So far so good????
October 14, 2011 at 6:23 am
CREATE PARTITION FUNCTION InventoryFinishedGoods (char(3))
AS RANGE LEFT FOR VALUES ('DFG', 'MFG', 'AFG');
Just a thought (may be outside OP’s requirements).
However Partitioning supports multiple data types but I assume it performs better with numbers (specially integer). Any comments on this?
October 14, 2011 at 6:35 am
If that's the case, I could make them integers and just translate them using a look up table.
Thoughts?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply