Table Design questions (1 Viewer)

Newbie here... I think I've got the guts of my table designs okay, but there's a couple of nagging issues. Here's my "virtual" pen and paper exercise. The relationship chart and other Access views just don't cut for me. I had to create excel-style table views to see the data in action all in one eyeful:



So a couple of questions...

1. in the first table, there may be multiple common names in the real world sometimes. Probably not too frequently. Should these be normalized elswhere? Seems like potential overkill. I am hoping a comma-separated list or even space delimited will work for the right keyword searches. Haven't gotten that far in the end result output design yet.

2. In tblPlantings, I'm not sure how to prevent duplicate entries. In my mind, the cultivar and location and qty combine to create a single instance of a "planting" of a particular species. I can have multiple instances of the same species and cultivar in different locations, but not at the same location. If there are multiples at the same location, that should just bump qty of a planting. I surely don't want a separate row for each "1" of qty.

When I read about a data model of animalType (cat ,dog, horse) and Pet (fluffy, fido, carrot), that related to my data nicely. But my plantings don't have Pet names. (although sometimes I think of them like my pets! ) Oh, now that I think of it, I wouldn't have two cats named fluffy.

hmmm...not quite there yet after all. Any ideas?

rereading my post and maybe its not so bad after all... If a plant dies and I replace the same plant in the same location, that would be a valid so-called "duplicate" planting. So I guess status is part of the combination that dictates uniqueness.

Is there a mechanism where I can check these things out before a new record is inserted? is that what a constraint is?

Maybe you do need to record individual plants. otherwise it looks like you will have to kill all the others in the planting if one dies since you have only one death date. Armagarden?

A lot depends on the size of the database and the way you want ot use it.

I would possibly consider normalization of the genera particluarly if you anticipate many plants of the same genus. This would speed any search for records of a particular genus and ensure that the spelling was totally consistent. With a Latin name there is always a chance that it could be entered incorrectly.

I would also consider relating the cultivars to the SpeciesID. This would allow you to select the cultivatar with a cascading combobox after pickiing the genus then species.

I would probably keep the common names as you have.

The search criteria to find the string anywhere would be:
Where [Common] Like "*" & [Forms!formname!search_box] & "*"

Otherwise you could have a Common table with records related to the SpeciesID.

anywhere you repeat text is a candidate for a look up table at least

in your first table, you have two entries for genus clethra - much better to have a look up table for your genuses - that way you ensure integrity. if you dont have a lookup table, you may get variant spelling of the genuses - which will interfere with your data management.

same thing in your plantings table - where you DO have a variant spelling - cultivar and cultiviar

gemma-the-husky said:

same thing in your plantings table - where you DO have a variant spelling - cultivar and cultiviar

Click to expand...

Cultivar is obviously a spelling problem.
In my post, I included another variant: cultivatar.

Galaxiom said:

Maybe you do need to record individual plants. otherwise it looks like you will have to kill all the others in the planting if one dies since you have only one death date. Armagarden?

Click to expand...

good point. (and good pun!) argggghhhh that would be a lot of single entries. Design-wise its always best to plant in multiples and groups of the same plant. Maybe I'll just forget about deaths. Other than the fact that "plantings" may grow in qty or shrink in qty but will not generate additional line items. THe "planting" could"die" when qty is 0. Will have to think about that....

Galaxiom said:

I would possibly consider normalization of the genera particluarly if you anticipate many plants of the same genus.

Click to expand...

gemma-the-husky said:

in your first table, you have two entries for genus clethra - much better to have a look up table for your genuses -

Click to expand...

I was thinking of using the kind of combo control where you can type into it as well as choose. If I don't already have one of the genera in the combo box pulldown, it can be created on the fly during a new species addition. element 0 would be like so:
"--select or enter new--"
Does that work? If not, probably would not be too hard to create a genera lookup list. Might still need a way for a user to add a new one if I left one out of my list.

At first I was going to use a single field "LatinName" instead of two genus and specificEpithet fields because there is not a lot that differs between them in other columns. But I decided it should be separate fields just in case. seems cleaner. And when I did my sample exercise, I indeed saw that the common name does vary within the same genus. I had not really been aware of that before.

Galaxiom said:

I would also consider relating the cultivars to the SpeciesID.

Click to expand...

they are. Column 2 of the plantings table. is that what you mean?

gardengal444 said:

I was thinking of using the kind of combo control where you can type into it as well as choose. If I don't already have one of the genera in the combo box pulldown, it can be created on the fly during a new species addition. element 0 would be like so:
"--select or enter new--"
Does that work? If not, probably would not be too hard to create a genera lookup list. Might still need a way for a user to add a new one if I left one out of my list.

Click to expand...

Check out the On Not In List Event property of the combo.

they are. Column 2 of the plantings table. is that what you mean?

Click to expand...

I meant a separate table where cultivars are related to a particular species so that only cultivars applicable to that species are allowed to be selected. So, for example, hummingbird cannot be chosen as a cultivar of Bleeding Heart.

Galaxiom said:

Check out the On Not In List Event property of the combo.

Click to expand...

Will do. thanks. I was thinking of maybe using Infopath for a front end. They probably offer something similar. I have to do some reading up. (Haven't even started and I am planning for the migration )

Galaxiom said:

I meant a separate table where cultivars are related to a particular species so that only cultivars applicable to that species are allowed to be selected. So, for example, hummingbird cannot be chosen as a cultivar of Bleeding Heart.

Click to expand...

They don't work like that. new cultivars are constantly being introduced and are specific to their species. There could conceivable be a 'hummingbird' cultivar of Bleeding Heart someday. There are many duplicate cultivar names today that belong to different species. 'alba', 'variegata', 'Lemon Twist' come to mind. And many plants don't have a cultivar name at all! Cultivars are an optional subset of species.

Nature and databases... interesting mix indeed!

Which particular aspect of InfoPath functionality do you see as having advantages over using an Access front end?

I really can't say. I've never used either. Just looking at my options.

I did a small very preliminary prototype in Infopath and it went ok with very minimal reading/learning. I liked that. I have to do some more reading first though to get a better handle on it.

Why do you have CatID in tblSpecies? Is that a foreign key? Are all of those species types of cats?

speakers_86 said:

Why do you have CatID in tblSpecies? Is that a foreign key?

Click to expand...

yes it is. Is the convention to call it CatFK or some such thing?

speakers_86 said:

Are all of those species types of cats?

Click to expand...

You did fine, I just didn't think those were all types of cats. Keep the field as CatID. You want to stay consistent. Thats my opinion.

One thing I do though, which isn't a big deal if you do this or not, is list all of the foreign keys immediately after the primary key. This way when in the relationship window, it is easier to draw the lines and not miss any.

I really like the table you made. I am also very much into gardening, and I think that I would find something like this to be very useful. Maybe I will make a chart similar to this one, thanks for the great idea.