split a table into two (1 Viewer)

[maxmangion]

I have a table in a database and I am planning to divide it in two tables. The current table contains information about stamps and at present it also contains 3 fields to contain the number of duplicates of each stamp. The table is as follows:

tblstamps: stampid, stampdescription, yearofissue, mintduplicates, usedduplicates, damagedduplicates etc.

Now I am planning to remove the last three fields from this table and create the following:

tblstamps: stampid, stampdescription, yearofissue etc

tblduplicates: stampid, duplicatetype, quantity

The main benefit which I see is that for each stamp that there are no duplicates there will not be three fields displaying 0, and the fact that now I will have a duplicatetype field instead, it will be more flexible to create even more duplicatetypes than just limited to those three types.

With the original design when it comes to the inputting of duplicates stamps, I have a tabular form where it has the following fields:

frmduplicates: stampid, mint, used, damaged Total
example 1 1 2 0 3

Therefore with the split tables now I would like to know if it is possible to keep a similar input form where the duplicatetype will be as column headings and each stamps will have just a single record displayed. I managed to achieve this by using a crosstab query, however, the problem is that it is not updateable.

Any suggestions how can i tackle this will be greatly appreciated.

Thank you

 

[namliam]

well.... yes and no... it is possible to make a query to allow the 'normal' input...

However since you need fixed number of columns and fixed column names you will either be limited in the # of columns you can use or be limited in the names you can use as your types, thus lose the flexibility your gaining.

 

[maxmangion]

thank you for your reply.

Actually the most thing I was originally worried about is that with the current set up I have a lot of records that has the 3 fields relating to duplicates with 0 as input so I am not sure if this goes against normalization. While it is true that with the new tables I will gain more flexibility in the amount of duplicatetypes I will create it is not particularly something that I wanted as a must because in 99.9% of time those three fields are more than enough (so i can live with it).

Therefore, if I had to leave those fields in the original table do you think i would be violating any rules of normalization or maybe encounter any performance issues when the number of records (stamps) will amount to several thousands?

Thank you.

 

[namliam]

Performance you wont have problems with either for even 999.999 records

Formaly doing anything that replicates like "Date1, date2, date3" or "error1, error2, error3" or "Dup type 1, Dup type 2, dup type 3" etc... goes against Normalization.

Question though is, will you have Dups with "perfect" condition? Or how will you inventory?
Normalization is a guide line that must be adheared to 99.99% but if you find decent reasoning to not do it, the dont. You will run into problems depending which ever way you go anyways.

 

[gemma-the-husky]

if you are just recording counts, then they surely belong in the main stamp table

the justification for a second table, is if you want to record the instances of the stamps in the first table

eg your stamp table has

ID 1, "Penny Black"
ID 2, "Penny Red"

now either store totals in this table,
penny black, 4 unused, 3 used, 3 damaged,
penny red, 2 unused, 7 used, 0 damaged,

OR

store 10 separate lines in another table

ID1 Item 1 (ie penny black) acquired whenever, condition new
ID1 Item 2 acquired whenever, condition used
etc

so you can generate the condition totals by counting the items in the sub table

-----------
second way is more work - just depends on how you need to get at your data, really, and what info you need

if you choose to just store totals in the main table, then of course you will get loads of zeroes - but this isnt a problem.

 

[maxmangion]

thank you both for the replies

if you are just recording counts, then they surely belong in the main stamp table

Exactly that's what I want to do. So I guess it will be better if I will leave the set up as it is (since after all it is working fine).

Just a final question: if I had the same scenario but rather than 3 fields they were 5 or more fields for example, does it still makes sense to keep them in the same main stamp table. Although I am not going into such detail in this database the following could all be valid fields to record duplicates

usedhinged, usedunhinged, unusedhinged, unusedunhinged, damaged, blocks etc.

Once again, thank you for your time to reply.

 

[The_Doc_Man]

You would use counts in the case where for a given item and quality, two or more items of the same quality are indistinguishable.

You would use separate records and do a Summation query when the individual items are distinguishable by some particular method, even if it is a self-imposed method such as putting a number on the envelope in which you store the stamps. (Maybe you wanted to remember how much each one cost?)