Need Suggestions (1 Viewer)

[Bert666]

Hi there fellow programmers.

I am designing an inventory database (First for me).

What i need to do is track input and output and finally the staock in hand.

Now i have come up with multiple ways of doing this - (Designing the structure). The first being:

Just one table to hold both the input and output with one field to identify each from the other, since both the ins and outs have 80% the same fields. But then i would be wasting the 20%.

The Second way:

Have two separate tables and then join them using a Union and get all the required fields - all calculations are done on reports.

Can anyone please help me decide - or have an alternative solution.

Bert.

 

[The_Doc_Man]

If I were doing this, I would vote for the UNION query because that way, the only time I mix apples and oranges is when I KNOW I'm about to make fruit salad.

It is better from the normalization standpoint to separate the records representing Inventory IN from those representing Inventory OUT.

The normalization rule is USUALLY read as "Do not include fields in your table that do not depend on your prime key." In this case, your table would have Inventory IN records that do not depend on ANYTHING related to Inventory OUT identifiers.

If you do it in a UNION query, then the only extra storage space we are talking about is some SQL, surely not exceeding a couple of hundred bytes. Whereas for any table with this interlaced data you are talking about, if it is not text data, then you are, indeed wasting database space with the extra unused fields.

Besides that, you gain further flexibility if you have to start tracking restock (returned) items separately. Don't know if that is part of your business, but if it is, that requirement would add a potential third table. Trying to retro-fit that into an existing, already sparsely populated table would be ugly. Adding it to a UNION query, on the other hand, is a piece of cake.

Since reports, forms, and even other queries can be based on queries; and since recordsets in code can be based on queries; and since EXPORT operations can even be based on queries, ... I see no disadvantages to using a UNION query for your situation.

One man's opinion, take it as such, but I happen to hold this particular opinion strongly.

 

[Bert666]

Thanks !!!

Hi there,

Thanks a lot for your suggestion and time I really appreciate it.

Bert.