normalizing data

[maxmangion]

I have a small database which takes care of my stamps collection. The main entities of the database are stamps, collectors and themes. Although the structure of my data works good and with reliable performance, I have a question regarding the stamps entity with regards to normalization.

Apart from the various attributes such as "face value", "country", "stamp description" etc, i also trace the amount of duplicates which I have. Right now my unnormalized structure with regards to duplicates is that for each record(stamp) I have the following attributes: "used", "mint", "cto", "damaged".

Eventually on the duplicates form I have a tabular form displaying the following:

stampid stampdescription   Year Value Used Mint CTO Damaged Total
1          stamp1         1980  8c     5     3      2    4    14
2          stamp2         2002  7c     1     2      0    2    5
etc

In the above scenario although everything works as expected, there might be the case where i will have too many records having a "0" value if no duplicates are available.

I was thinking that i should remove the above mentioned fields and create a new table as follows:

stampid, duplicatetype, amount

1           used              5
1           mint              4
1           damaged        0 (this record will not be created)
etc

Should this be a better approach, is there a way in which i can still have the duplicates form similar to my above example i.e. since the 0 records will not be created, would it be possible to have them available for viewing purposes to keep the same duplicates form structure.

Thank you for any advice.

 

[stopher]

I'd definately go for new suggested layout.

As you say, you will have stamps where there is no record for "damaged". You can deal with this in a crosstab query by setting the column headings. This forces the headings you choose to appear in the crosstab and be populated even if there are no records.

Hope I've understood your question.

Chris

 

[maxmangion]

Hi Stopher

Many thanks for your reply. To be honest I have thought of crosstab queries, however I have two problems with it. The first I managed to sort out thanks to your link where i can include the column headings myself. However, my other problem is that the amounts in the crosstab query are not updateable. Normally I update the amounts in the duplicate form and now if i base my form on this crosstab query i cannot make the updates there. i am attaching a very small sample database (with two tables, the query and the form) to illustrate what i mean.

Is there a way to make the crosstab query updateable?

Thank You

 

[stopher]

Sorry, I missed your requirement for data entry.

If you were to adopt the new structure then we're talk unbound forms or at least forms not bound directly to your tables. You'd have to code the fetch and putaway.

I've gone with a datagrid from componentone. I don't know if there is a limit on the free trial or whether you just get a pester pop-up.

At the end of the day you've got to ask yourself what you will gain by spending a lot of time fully normalising. If you're not planning to develop the database too far or you don't need the data fully normalised then by might be better sticking with what you've got. You I guess you can see that simply getting the number of stamps is slightly more work than it would be if normalised.

Chris

 

[maxmangion]

Thank you for your reply. Yes i think it's better to leave it as it. The reason that this database is for personal use and i am the sole user gives me more flexibility to leave things as they are.

However, should someone come up with a better solution, it would be greatly appreciated if you share it.

Thank you