creating a unique product

[bml337 via AccessMonster.com]

im creating a database for a florist who happens to do lots of events. Im
still pretty green to db design but im a quick study.

my problem, how to keep track of the arrangements (with multiple styles) for
each party. I need to know how many, type, style , flower ect. (see below)
and color to start. I cant predefine the product list since the combinations
are astronomical.

Here is partial table, the BOLD is the table header... and the lowercase is
the drop down options.

TYPE - arrangement, ceremony, CTH, personal
STYLE- tall, low, cube, bubble
STAND- none, crystal, silver, glass
FLOWER- roses, lilies, mum's, tulips
COUNT-(#of each flower in the arrangement)
COLOR- red, blue, green, pink
QTY-(# of arrangements)

basically i want to create a unique "product" using the options above. After
its created, i should be able to see all arrangements i created for that
clients event. ALSO, i need to create a market list which i can run at the
begging of the week. Basically, run a report which shows me all the flowers
i need to order for the coming weeks party.

If anyone could help, it would be greatly appreciated or point me in the
directions of a db or tutorial to show me how to do something like this would
be great!

thanks in advance

 

[Darryl Kerkeslager]

You have a good start. I suggest you read up on normalization; there are
hundreds of sites on the web.

In looking at your table - let's call it arrangement - I see that you have
defined a set of attributes. Some of these are very basic attributes -
STAND, for instance. This kind of attribute will more than likely be
selected on a form by a drop-down combo box. The four values can be placed
in a simple lookup table (two columns - stand_id and stand_type), or can be
entered as a value list (personally, I always use tables). Assuming that
you use the table method, what you store in the arrangement table, in the
arr_stand_id field, is just the stand_id, not the stand_type. This has many
benefits, among them that storing a number is more efficient, and if you
ever change the name of the stand to "glass, style A" then you don't need to
change the name in 257 occurrences in your arrangement table.

The TYPE attribute (field) is confusing, in that you seem to indicate
arrangement is a type of arrangement??

More troubling is the FLOWER attribute. This indicates that each
arrangement would only have one type of flower - and I'm no expert. but this
seems unlikely. I think that what you actually want is the possibility that
one arrangement can have more than one flower_type - and we must also
acknowledge that each flower_type can be in more than one arrangement - so
this defines what is known as a many-to-many relationship. Also note that
you have counts for each flower_type, so you need to track that also. You
will need to do some reading on database relationships.

You will find that you will need a structure similar to this:

arrangement
[arrangement_id]
[arr_style_id]
[arr_stand_id]


arr_ft
[arrangement_id]
[flower_type_id]


flower_type
[flower_type_id]
[ft_name]

 

[bml337 via AccessMonster.com]

Thanks for the response Darryl!

I actually started creating tables today for each attribute; each has an ID
as suggested. The “type†helps specify what that piece is for. For example,
arrangement would be for a table (dinner table), but if personal is selected
from the “typeâ€, that would mean personal flowers e.g. (for bridal party).
The “style†for an arrangement could be “lowâ€, which sits directly on a table
(not on a stand). If the “type†is personal, then a possible “style†could
be bouquet, corsage ect. I decided to remove the stand and create another
table “equipment†w/ID to list everything we rent out.

As you said I would want to select all these from a drop down menu to create
my DETAILED ID (guess “detailed†table make more sense).

You are correct; an arrangement could/does have multiple flowers in it, my
dilemma (guess im going to learn a lot on my first access db).

Im going to read up on one to many relationships and normalization. Do you
know of any sample db I can download and play around with to see how a one to
many db is configured?

You have a good start. I suggest you read up on normalization; there are
hundreds of sites on the web.

In looking at your table - let's call it arrangement - I see that you have
defined a set of attributes. Some of these are very basic attributes -
STAND, for instance. This kind of attribute will more than likely be
selected on a form by a drop-down combo box. The four values can be placed
in a simple lookup table (two columns - stand_id and stand_type), or can be
entered as a value list (personally, I always use tables). Assuming that
you use the table method, what you store in the arrangement table, in the
arr_stand_id field, is just the stand_id, not the stand_type. This has many
benefits, among them that storing a number is more efficient, and if you
ever change the name of the stand to "glass, style A" then you don't need to
change the name in 257 occurrences in your arrangement table.

The TYPE attribute (field) is confusing, in that you seem to indicate
arrangement is a type of arrangement??

More troubling is the FLOWER attribute. This indicates that each
arrangement would only have one type of flower - and I'm no expert. but this
seems unlikely. I think that what you actually want is the possibility that
one arrangement can have more than one flower_type - and we must also
acknowledge that each flower_type can be in more than one arrangement - so
this defines what is known as a many-to-many relationship. Also note that
you have counts for each flower_type, so you need to track that also. You
will need to do some reading on database relationships.

You will find that you will need a structure similar to this:

arrangement
[arrangement_id]
[arr_style_id]
[arr_stand_id]

arr_ft
[arrangement_id]
[flower_type_id]

flower_type
[flower_type_id]
[ft_name]

im creating a database for a florist who happens to do lots of events. Im
still pretty green to db design but im a quick study.

[quoted text clipped - 34 lines]

thanks in advance

 

[Darryl Kerkeslager]

Use care in the naming of tables. A table describes an object. "detailed"
is not an object. While 'detail' may be an object, it is a bit abstract,
and you are describing very concrete things. This may seem nitpicking, but
if your table names accurately reflect what is in your table, and the names
are the names of singular nouns (flower_type, equipment, etc), then each it
makes it much more evident when you get off-track.

Off course, the bridge tables used for many-to-many relationships will
violate that rule, but other than that, my table names are always very
simple, concrete, one word if possible.
If you use more than one word, use an underscore to separate the two words
so that you never have to use the [ ] around a table name in a query.
Always use lower case for table names and field names; it makes your queries
easier to read.
Always have an id field even in simple tables.
Always use autonumber as your id field type. Some would argue against this,
but it forces you to have id fields that are meaningless numbers, which is a
good thing, because the keys used in joins should never be 'real' numbers
like SSN or part numbers.

As to a sample db, Microsoft's northwind.mdb is a good place to start.

--
Darryl Kerkeslager

As you said I would want to select all these from a drop down menu to
create
my DETAILED ID (guess "detailed" table make more sense).

You are correct; an arrangement could/does have multiple flowers in it, my
dilemma (guess im going to learn a lot on my first access db).

Im going to read up on one to many relationships and normalization. Do
you
know of any sample db I can download and play around with to see how a one
to
many db is configured?

You have a good start. I suggest you read up on normalization; there are
hundreds of sites on the web.

In looking at your table - let's call it arrangement - I see that you have
defined a set of attributes. Some of these are very basic attributes -
STAND, for instance. This kind of attribute will more than likely be
selected on a form by a drop-down combo box. The four values can be
placed
in a simple lookup table (two columns - stand_id and stand_type), or can
be
entered as a value list (personally, I always use tables). Assuming that
you use the table method, what you store in the arrangement table, in the
arr_stand_id field, is just the stand_id, not the stand_type. This has
many
benefits, among them that storing a number is more efficient, and if you
ever change the name of the stand to "glass, style A" then you don't need
to
change the name in 257 occurrences in your arrangement table.

The TYPE attribute (field) is confusing, in that you seem to indicate
arrangement is a type of arrangement??

More troubling is the FLOWER attribute. This indicates that each
arrangement would only have one type of flower - and I'm no expert. but
this
seems unlikely. I think that what you actually want is the possibility
that
one arrangement can have more than one flower_type - and we must also
acknowledge that each flower_type can be in more than one arrangement - so
this defines what is known as a many-to-many relationship. Also note that
you have counts for each flower_type, so you need to track that also. You
will need to do some reading on database relationships.

You will find that you will need a structure similar to this:

arrangement
[arrangement_id]
[arr_style_id]
[arr_stand_id]

arr_ft
[arrangement_id]
[flower_type_id]

flower_type
[flower_type_id]
[ft_name]

im creating a database for a florist who happens to do lots of events.
Im
still pretty green to db design but im a quick study.

[quoted text clipped - 34 lines]

thanks in advance

 

[Guest]

Good info Darryl.

There are a couple of points I would disagree with. In most database
engines, to separate table field names from variables, you will see field
names done in upper case with the underscore separating words.
EMPLOYEE_NAME
CONTRACT_ID

As to table names, I would recommend using uppercase only to delimit words
within the name:
tblContractMaster
tblContractDetail

Also, for all naming it helps everyone if you use Reddick naming
conventions. It quickly identifes the type of object it is and it is a
99.44% certainty you will not end up using a reserved word.
--
Dave Hargis, Microsoft Access MVP

Use care in the naming of tables. A table describes an object. "detailed"
is not an object. While 'detail' may be an object, it is a bit abstract,
and you are describing very concrete things. This may seem nitpicking, but
if your table names accurately reflect what is in your table, and the names
are the names of singular nouns (flower_type, equipment, etc), then each it
makes it much more evident when you get off-track.

Off course, the bridge tables used for many-to-many relationships will
violate that rule, but other than that, my table names are always very
simple, concrete, one word if possible.
If you use more than one word, use an underscore to separate the two words
so that you never have to use the [ ] around a table name in a query.
Always use lower case for table names and field names; it makes your queries
easier to read.
Always have an id field even in simple tables.
Always use autonumber as your id field type. Some would argue against this,
but it forces you to have id fields that are meaningless numbers, which is a
good thing, because the keys used in joins should never be 'real' numbers
like SSN or part numbers.

As to a sample db, Microsoft's northwind.mdb is a good place to start.

--
Darryl Kerkeslager

As you said I would want to select all these from a drop down menu to
create
my DETAILED ID (guess "detailed" table make more sense).

You are correct; an arrangement could/does have multiple flowers in it, my
dilemma (guess im going to learn a lot on my first access db).

Im going to read up on one to many relationships and normalization. Do
you
know of any sample db I can download and play around with to see how a one
to
many db is configured?

You have a good start. I suggest you read up on normalization; there are
hundreds of sites on the web.

In looking at your table - let's call it arrangement - I see that you have
defined a set of attributes. Some of these are very basic attributes -
STAND, for instance. This kind of attribute will more than likely be
selected on a form by a drop-down combo box. The four values can be
placed
in a simple lookup table (two columns - stand_id and stand_type), or can
be
entered as a value list (personally, I always use tables). Assuming that
you use the table method, what you store in the arrangement table, in the
arr_stand_id field, is just the stand_id, not the stand_type. This has
many
benefits, among them that storing a number is more efficient, and if you
ever change the name of the stand to "glass, style A" then you don't need
to
change the name in 257 occurrences in your arrangement table.

The TYPE attribute (field) is confusing, in that you seem to indicate
arrangement is a type of arrangement??

More troubling is the FLOWER attribute. This indicates that each
arrangement would only have one type of flower - and I'm no expert. but
this
seems unlikely. I think that what you actually want is the possibility
that
one arrangement can have more than one flower_type - and we must also
acknowledge that each flower_type can be in more than one arrangement - so
this defines what is known as a many-to-many relationship. Also note that
you have counts for each flower_type, so you need to track that also. You
will need to do some reading on database relationships.

You will find that you will need a structure similar to this:

arrangement
[arrangement_id]
[arr_style_id]
[arr_stand_id]

arr_ft
[arrangement_id]
[flower_type_id]

flower_type
[flower_type_id]
[ft_name]

im creating a database for a florist who happens to do lots of events.
Im
still pretty green to db design but im a quick study.
[quoted text clipped - 34 lines]

thanks in advance

 

[Tim Ferguson]

Off course, the bridge tables used for many-to-many relationships will
violate that rule,

I don't think I but this either. These tables generally _do_ represent
real-world things just as much as FlowerTypes or Products. Examples would
be

tblStudentCourse ... should be ... Registrations

tblProductOrder ... should be ... OrderLines

tblSurgeonPatient ... should be ... Operations

and so on. It does not really take much thinking about the actual
relationship between the actual entities to produce a suitable name. The
problems with using names like tblJunctionOneThingAnotherThing are

- It encourages functional thinking rather than modelling. "How do I get
that to relate to the other in this database?" If you get the model
right, the relationships follow naturally and easily.

- It fails to describe what the relationship actually describes.
tblStudentTeacher could be who-is-taught-by-whom; who-is-assigned-to-
which-tutor-group; who-was-reported-for-bad-behaviour; etc; etc.

- It pretends that there is some kind of difference between "junction
tables" and other kinds, which does not exist. Any table that has two (or
more) foreign keys could be a junction table. What about tblColourCountry
instead of Vehicles?


All tables are equal. No tables are more equal than others. One prefix
bad, two prefixes worse.


All the best


Tim F

 

[Guest]

Reddick naming conventions suggest assigning prefixes to all objects:
frm = Form
cbo = Combo Box
qsel = Select Query
qapp = Append Query
tbl = Table

tblStudentCourse ... should be ... tblRegistrations

tblProductOrder ... should be ... tblOrderDetails

tblSurgeonPatient ... should be ... tblSurgeries

Operation does not always mean a surgery.

 

[Darryl Kerkeslager]

tblProductOrder ... should be ... OrderLines

But what is an orderline?

Hmmm ...

M-M table of networked printers and rooms in an office?

M-M table of zip_codes with acceptable_names?

In fact, according to the Kerkeslager theory of infinite nouns, if there are
an finite number of nouns, and each noun can be paired with any other by an
infinite number of many-to-many relationships, then you will eventually
reach a point where you cannot describe the joined table by a known noun ...
so you will have to invent words like "orderlines".

 

[Darryl Kerkeslager]

There are a couple of points I would disagree with. In most database
engines, to separate table field names from variables, you will see field
names done in upper case with the underscore separating words.
EMPLOYEE_NAME
CONTRACT_ID

IMLE, most SQL statements are written in caps, therefore a complex select
statement becomes much more easy to read with tables and fields in small
letters, SQL words in CAPS, and functions in Mixed Case.

As to table names, I would recommend using uppercase only to delimit words
within the name:
tblContractMaster
tblContractDetail

While I do use Reddick for Access objects like forms and controls, NOT using
Reddick lets me know quite easily that I am referring to a field or table.
I never use Reddick with variables and constants, except to distinguish two
variables that have been used in a data conversion. Since most code blocks
are short, this almost never confuses me ... wait, or does it ...?

I am aware of the issues with reserved words, and admittedly have been
tripped up a few times, but for theclarity of the SQL, it seems wirth it. I
also use a very careful naming convention with tables and field names, which
tells me very clearly what I need to know most every time, and eliminates
having to fully qualify filed names:

Each table must have a name (obviously) and an abbreviated name that is
unique in the db:

offender ... off
supervision ... sup

the id field is always the table + underscore + "id":

offender_id
supervision_id

all other fields (except two) are prefixed with the abbreviated name +
underscore:

off_name
sup_term

The two fields that do not use the prefix are the same in every table:

updated
update_by

Bridge tables are named by joining the two prefixes:

off_sup (or maybe sup_off)

The foreign keys are named by ading the unchanged table name to the field
(wordy, but how many times do you really use the bridge table field names?):

off_sup_supervsion_id
off_sup_offender_id


So, I can quite easily tell that txtOffName is the control that holds the
value of the off_name field, and if see off_name, I am reminded quikly that
it is part of table offender.

 

[Tim Ferguson]

Reddick naming conventions suggest assigning prefixes to all objects:

And, when applied to database objects rather than programming variables,
brings Access more oppropbrium in the wider database world than almost
anything.

Quite apart from the fact that it is completely inappropriate in a hard-
typed language like VBasic;

Quite apart from the fact that the apps-hungarian monster grew out of a
gross failure to understand the point of systems-hungarian in the first
place;

Quite apart from the fact that when I change my database so that
tblSomething is now really a Querydef or a View, I have to rewrite the
whole application; which is precisely what qdefs and views are there to
avoid;

Quite apart from all that, it makes Access look like a toy for people who
are not to be taken seriously. And that's not fair, because it isn't and
we are (or should be) and I mind that like hell. Even Microsoft itself
has seen the error of this disaster and coding conventions for vb.net
have chucked the whole system over the side. RIP to that.

Just a thought


Tim F

 

[Tim Ferguson]

But what is an orderline?

Look at NorthWind.

Anyway, what I wrote was OrderLines not orderlines.

M-M table of networked printers and rooms in an office?

SituatedIn... how can a printer be situated in more than one room at a
time?

M-M table of zip_codes with acceptable_names?

Don't understand. What would be acceptable about a name that applied to
more than one zip code?

so you will have to invent words like
"orderlines".

And what is wrong with invented nouns anyway? This is a semantic
modelling convention, not a spelling bee.


Tim F

 

[Darryl Kerkeslager]

Don't understand. What would be acceptable about a name that applied to
more than one zip code?

"Richmond, VA" applies to about 30 zip codes. We find that mostly
acceptable

"Ettrick, VA" and "Petersburg, VA" both apply to zip code 23803. USPS finds
that acceptable.