How should I use DynamoDB table

You could try to build a fully table-driven rules engine, sometimes also called an inference engine, but you would be much better off buying one than trying to build your own, because they are very complex.

If the cost and/or complexity of a fully table-driven rules engine is too high for your organization, then you can build a compromise solution. The compromise involves mixing tables and procedural (code based) rules. The objective is to optimize the balance between flexibility and maintainability.

The way to achieve this is to carefully examine all of your current rules, taking into consideration as much as possible any potential rules that aren't used now, but which might come into effect in the future. This analysis should give you a set of rules for doing your payroll calculations.

You then need to break these rules down into two categories: (i) rules to be based in procedural code and (ii) rules to be table-driven. The best way to do this is to keep scalars in tables and more complex rules in code. For example the fact that one amount can be based on another amount is a good candidate for a code-based rule. The fact that the derived amount is 20% of the base amount is a good candidate for a table-driven rule.

For practical purposes, your rule tables will have a structure that includes the following:

• Rule Type: This is a value that tells your procedural code which function/code-based rule uses the scalar data in a particular record.

• Scalar Value: This is a numeric value that has a meaning which is understood by the procedural code indicated by the Rule Type.

• Business Identifier: This is an indicator of which business element the rule applies to. Like the Scalar Value the Rule Type indicates which procedural code uses the rule record and that procedural code knows what to make of the Business Identifier. It might be a department number, an employee ID, a job code or whatever else your rules require to link payroll amounts to employees. Your rules table may need more than one Business Identifier, depending on how your rules work, or your requirements might dictate that business identifiers are normalized out into an intersection table.

• Effective Date: One thing you can bet on is that your payroll scalars will change over time. Your system is going to need a record of what the rules used to be so you can make corrections to your payroll. Also, for the sake of the sanity of your HR and IT staff, you need to be able to record new rules before they go into effect.

• Expiry Date: This completes the effective date range of the rule. I'd recommend using a max collating value so that every record has a fully qualified effective range. That will make your queries much simpler.

One of the realities of a solution like this is that you will have to come back to build new code-based rules from time to time as your organization's payroll policies change. Don't be discouraged by this. Even if you had a fully table-driven rules engine, changing payroll policies would entail making changes to the rules data. Business changes entail system changes - that's a fact of life.

You want to store an undefinied number of items in a list, for this you need a new table or cleaner two tables.

you already have the first table called items, each has an item_id.

Then one table holds all the references to the collections, so best case only a new collection_id for every of the collection.

And the important one is the MxN relation table between items and collections, let's call it collection_items, holding in each row one item_id and one collection_id.

If you want to have multiple same items in one collection, and only want to know how many, add a count field.

But if the relations need to be distinguishable, add a row for every relation and an extra collection_item_id.