Opinion time... (or maybe it's a solid fact and I missed the memo?)
Back in DBF days, when I had a file, for example "inventory" that would
have ongoing additions, I would have two (or more) tables:
Table 1: On-hand inventory available to sell (i.e., "onhand")
Table 2: Inventory that was sold (i.e., "sold")
Obviously, when something was sold, the record would be 'moved' from
Table 1 to Table 2 by reading it from 1, writing it to 2, then deleting
it from 1.
I did it this way so that File 2 could grow over the years and be
available as historical data when needed, but for most daily operations
File 1 would be where the action was (receiving new and selling
existing). This approach would keep File 1 smaller, which would make it
faster and easier to manage.
Now, I've switched everything to client server with MariaDB as the
backend using the InnoDB engine.
Is there any advantage to the 'old' approach?
Will there be any degradation as the single "All Inventory" file grows,
and grows, and grows?
Is it 'dangerous' to put all the eggs in one basket?
I'm thinking due to the client-server design, that it should work like
this...
1. Receive piece of merchandise, add it to "All Inventory" table (date
received, price, etc.) which also has an empty field for "sold"
2. When an item is sold, just update that one and only record for that
unit with the sales document # of the sales ticket.
Done!
Am I creating a bottleneck for data access? Obviously the one table will
be used to query ALL inventory questions...is something in stock? How
many have been sold? How many are not sold? Since I'm using InnoDB
tables there shouldn't be any locking issues...
If there are benefits and disadvantages to each approach, is one the
preferred approach?
Advice? Opinions?
Thanks in advance for your wisdom shared!
Mike Copeland
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
