Re: When to use integer keys?

> You haven't given any sound reason for preferring either one table or
> two.

I haven't given any because I don't know what factors would influence this decision. What factors should I be looking at? Sorry if I'm asking stupid questions here, but I know very little about database design (most of my formal training is as a programmer).

> We can't know for sure that it will save storage space without
> knowing the internals of the (unspecified) DBMS product you are using.
> Nor is it clear that the savings on storage alone would justify the
> additional complexity of an extra table.
>

Sure. Agreed. If you're interested, the DBMS is Access although in the future, this database may be migrated to a different one. I believe it is likely to save space to use two separate tables because the average length of name is likely to be around 15 characters whereas the integer key would be 4 bytes and there are about 20 different block names whereas there are multiple hundreds of blocks.

But you are right that that space doesn't really justify additional complexity. Are there other reasons someone would do this? What are they?

> The prudent course is to design a logical model independently from
> storage considerations. Storage requirements are determined by the
> physical model not the logical.

Sure. Perhaps I shouldn't have brought up storage at all. It doesn't concern me greatly. How do I decide within a logical model whether a name is a separate entity or a description of a given entity? In my case, it may be reasonable to think of names as separate entities because there are a very limited number of them. Am I even thinking about this correctly? Received on Tue Jan 23 2007 - 22:43:01 CET