Software

Ask something
Discussion Locked

Database design question

0 helpful
1 reply

by powlboyjr

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am currently updating an old MS Access Inventory database. I was wondering what the recommendation would be for inventory items when they are disposed. Would you create two tables (i.e. CurrentAssets, DisposedAssets) or would it be more efficient to add another field called Status? I would think the Status field would be more efficient. As of right now an item is copied to DisposedAssets, then deleted from CurrentAssets using a macro. Also, if I use the Status field, would I link it to a table, for example tblStatus, and put a StatusID field in this table along with a Status field (values=Active, Inactive, Disposed)? Thank you very much.

1 reply

by tony hopkinson

so to me the criteria for judging which way to go would be the operations you are carrrying out on the data.

If in the main you are operating with not disposed items, I'd go for the two tables as that avoids having to select not disposed every time you want to select a list of available items.
In that way disposed is more of an archive.

However for historical reports (say transactions on items) having two tables will force you to use unions within joins

You could also look at a data warehousing type idea, there you would keep that data split for standard operations, and then have it merged in to another fit for purpose table(s) for reporting purposes.

Whether that is of any real benefit, would depend on the volume of data and the number of 'operations'

It's probably best guess time.
You could use stored procedures and views to hide this implementaion detail, then you could easily change the implementation should it become necessary. Whether the increased complexity could be worthwhile, I couldn't say from all the way over here.

HtHs

This discussion is closed to new replies.