Your Opinion on Design Question

[dhstein]

I'll try to describe the situation pretty thoroughly and then I have a pretty
specific question. For an Inventory database, we are trying to keep track of
2 pieces of information. One piece is the quantity of each item (each item
is defined by an SKU number). The other piece is the location of each item.
Items can be and will be in more than 1 warehouse. There are 4 warehouses
and each warehouse has hundreds of locations. One table is the Product Table
which defines Vendors and other pieces of information. Now I want either of
these two configurations:

A) Multiple Tables
1) Locations Table which contains a SKU and information about which
warehouse or warehouses hold the item
2) Warehouse Tables - a separate table for each warehouse containing
information about shelves and specific SKU numbers and quantities

B) A Single Table
This table would contain all location information about the SKU
locations. Which warehouses it is in and which locations in the warehouse.


It seems to me that the first choice is better, but maybe I'm wrong - what's
your opinion? Thanks.

 

[Douglas J. Steele]

Option A is the only appropriate one.

You have a classic many-to-many relationship (one product can be kept at
many different warehouses, and one warehouse can keep many different
products).

To resolve many-to-many relationships, you need 3 tables: one to hold the
details on the products, one to hold the details on the warehouses, and one
to hold which products are being stored in which warehouses.

 

[Tony Toews [MVP]]

There are 4 warehouses
and each warehouse has hundreds of locations.

I strongly suggest multiple tables because what happens when your
employer adds a 5th warehouse? Also it's much easier to find the
current locations of all of a particular item by reading multiple
records down rather than have to programmatically read sideways on a
single table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[dhstein]

I strongly suggest multiple tables because what happens when your
employer adds a 5th warehouse? Also it's much easier to find the
current locations of all of a particular item by reading multiple
records down rather than have to programmatically read sideways on a
single table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Thanks for the information Tony

 

[dhstein]

Option A is the only appropriate one.

You have a classic many-to-many relationship (one product can be kept at
many different warehouses, and one warehouse can keep many different
products).

To resolve many-to-many relationships, you need 3 tables: one to hold the
details on the products, one to hold the details on the warehouses, and one
to hold which products are being stored in which warehouses.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)





Thanks for your response Doug.

 

[dhstein]

TblVendor
VendorID
<Vendor contact fields>

TblProduct
ProductID
ProductName
SKUNumber
VendorID
<Other product desc fields>
ProductPrice

TblWarehouse
WarehouseID
WarehouseName
<Warehouse location fields>

TblWarehouseLocation
WarehouseLocationID
WarehouseID
WarehouseLocation

TblProductWarehouseLocation
ProductWarehouseLocationID
WarehouseLocationID
ProductID
ProductInventory

I assumed the price of any product is independent of the warehouse it is in.

Steve






Steve thanks for the information and the table design.

 

[Tony Toews [MVP]]

TblVendor

Although, as I've posted in the past, I completely disagree with using
any kind of object prefixing such as tbl other than in VBA variables.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tonysobjectnamingconventions.htm

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Michael Gramelspacher]

Although, as I've posted in the past, I completely disagree with using
any kind of object prefixing such as tbl other than in VBA variables.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tonysobjectnamingconventions.htm

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony

If Steve were to drop the TblVendor nonsense and just name the table Vendors, if would be a giant
step in conveying the idea that he is truly a competent database professional. Appearance counts
for a lot when you are trying to sell yourself. Even though a database with names like TblVendor
could be designed correctly, the Tbl Vendor creates an unnecessary barrier for many people.

Of course I realize that prefixes Tbl, tbl, tbl_ , etc. have their defenders.

 

[John... Visio MVP]

I know this may be a hard concept for you, but simply put, tables are
collections of objects and queries have actions.
So Vendors would be a table, Vendor a record in that table and NewVendors a
query.

John... Visio MVP

 

[Larry Daugherty]

Are you now going to jump in and object to every response with object
prefixes embedded? You made that same point today in another
newsgroup when it lay slightly nearer the OP's issue. In this case,
it's far removed from the issue..

Shall we who embrace the Reddick naming convention jump on each of
your posts and make a big deal out of the fact that you *don't support
the most widely adopted naming convention for Access, VB and VBA*? We
would be equally justified. More so, I believe. The more widely
adopted the naming convention, the greater its value. Note that has
nothing to do with the purity of the form nor with abstract "truth".

As you know, you're free to create and embrace any naming convention
whatever, including none at all. Everyone else has that same right to
suit their own preferences. But, the bottom line for these newsgroups
is *best help for OP".

I suggest that we all propose solutions or ideas to OP according to
our habits or as we believe it will best serve OP. I believe that the
Reddick convention, which is the one most widely published and
adopted, best serves novice and intermediate Access developers.
Advanced developers will keep on using/doing whatever got them to that
point.

Too bad that your eagerness to refer to your own practices on your
site caused you to single out a post of Steve's. Others then piled
on. I don't know if you've been aware but his behavior has improved a
whole bunch over what it was in years past. He has been doing a lot
more responsible posting without the constant hustle of the unwary.

Yes, it seems he can't help himself at times. Even at those times,
I'd simply warn OP and just ignore Steve. John ...Visio is on him
anyway.

HTH

 

[Keith Wilby]

I know this may be a hard concept for you, but simply put, tables are
collections of objects and queries have actions.
So Vendors would be a table, Vendor a record in that table and NewVendors
a query.

Hi John.

Are you saying that a select query based on a table named Vendors would be
called "NewVendors"? What would you name an Append query based on the same
table?

Regards,
Keith.
www.keithwilby.co.uk

 

[John... Visio MVP]

Hi John.

Are you saying that a select query based on a table named Vendors would be
called "NewVendors"? What would you name an Append query based on the
same table?

Regards,
Keith.
www.keithwilby.co.uk

I was thinking in terms of a generic query, something to differentiate a
query from a table. An Append Query for the same table could be AddVendors.

Queries are either a subset of a table or joined tables. There is always
some qualifier (New, Add) that indicates what part of the table you want.
For appends I like the qualifier to be something that indicates what is
happening (like Add). "New" for me would indicate selection. You want the
Vendors who are New. So long as the names are suitable, the "tbl" prefix is
not really necessary. I look at it as the formality of placing "Mr" in front
of a name. If you want a more formal environment, then use the prefix, but
in most cases you should be able to look at someone and determine if they
are male of female. (Note, I did say "most" cases. There are exceptions.)

John... Visio MVP

 

[David W. Fenton]

If Steve were to drop the TblVendor nonsense and just name the
table Vendors, if would be a giant step in conveying the idea that
he is truly a competent database professional.

That's a completely unwarranted assumption, and, I think, says more
about the baggage you're carrying into the discussion than it does
about the merit of any particular answer to the question itself.

In the Access development world, it is standard practice, and to me,
an Access developer who *doesn't* use it is the one who looks
unprofessional. There are very good reasons for it in Access that
derive in part from the application development environment and the
way it presents information (and combines multiple namespaces in
certain contexts).

Now, I don't mean to imply that Tony is unprofessional -- he's
obviously not, but his naming conventions always struck me as a
quirk particular to *him*.

And I think he's the bees knees despite it.

 

[David W. Fenton]

I know this may be a hard concept for you, but simply put, tables
are collections of objects and queries have actions.
So Vendors would be a table, Vendor a record in that table and
NewVendors a query.

I think table names should be singular, as the table models a
particular entity, of which there are multiple instances (records)
stored in it. Many people use the convention of tbl + EntityName +
ID where the first pair give you the table name, and the last pair
the PK name of the table. Thus, the PK name of tblPerson would be
PersonID. If you use a plural table name, you get tblPeople and
PeopleID, the last of which would be referring to a single person.

Of course, all of this depends on what conventions you use. If you
don't name your PK fields that way, it won't justify the singular
table name. But if you *do* use that convention (as many, many
Access developers do), it is rather illogical to use the plural in
the PK name.

 

[David W. Fenton]

So long as the names are suitable, the "tbl" prefix is
not really necessary. I look at it as the formality of placing
"Mr" in front of a name. If you want a more formal environment,
then use the prefix, but in most cases you should be able to look
at someone and determine if they are male of female. (Note, I did
say "most" cases. There are exceptions.)

Seems to me that your suggestion of Vendors and NewVendors makes no
sense, as it sorts the two far apart with no real benefit. Do you
really need to have all your queries for new records (or whatever
you're defining "New" to mean in your app) sorted together? Wouldn't
it be easier to have Vendors and VendorsNew, so objects based on the
same table(s) sort together?

My linked tables have several prefixes, such as tbl, tmp and vbl
(for SQL Server views), among others. I seldom use anything but qry
for queries, though. I don't find the loss of sorting base tables
together to be problematic. I remember the days before I adopted
prefixes and how complicated my names became for queries in
particular. I still have some of the same problem, but don't worry
about it nearly as much! Once you have more than a couple of queries
with the same base table(s), you run into that problem regardless of
whether or not you use a prefix, and that's a much harder issue to
solve.

 

[John... Visio MVP]

I think table names should be singular, as the table models a
particular entity, of which there are multiple instances (records)
stored in it. Many people use the convention of tbl + EntityName +
ID where the first pair give you the table name, and the last pair
the PK name of the table. Thus, the PK name of tblPerson would be
PersonID. If you use a plural table name, you get tblPeople and
PeopleID, the last of which would be referring to a single person.

Of course, all of this depends on what conventions you use. If you
don't name your PK fields that way, it won't justify the singular
table name. But if you *do* use that convention (as many, many
Access developers do), it is rather illogical to use the plural in
the PK name.

I guess we agree to disagree. I always consider tables to be collections of
items and so use a plural name for a table. To access the table, the PK
points to a record (a single item), so I would use the singular for the PK.
So a table called People would have a PK of PersonId. I tend towards to the
proper English terms rather than Entity names.
Find Customer in Customers
rather than
Find pkCustomerId in tblCustomer

My excuse was that I started this game long before naming conventions were
even considered. Somewhere around here I have the chisel and stone tablet I
used for programming.

John... Visio MVP

 

[Keith Wilby]

Now, I don't mean to imply that Tony is unprofessional -- he's
obviously not, but his naming conventions always struck me as a
quirk particular to *him*.

And I think he's the bees knees despite it.

You've summed it up for me there, I always use prefixes in names and
nothing's going to change that because it helps me enormously in code, but
the fact that Tony does otherwise doesn't diminish my respect for him.

Keith.

 

[Michael Gramelspacher]

That's a completely unwarranted assumption, and, I think, says more
about the baggage you're carrying into the discussion than it does
about the merit of any particular answer to the question itself.

In the Access development world, it is standard practice, and to me,
an Access developer who *doesn't* use it is the one who looks
unprofessional. There are very good reasons for it in Access that
derive in part from the application development environment and the
way it presents information (and combines multiple namespaces in
certain contexts).

Now, I don't mean to imply that Tony is unprofessional -- he's
obviously not, but his naming conventions always struck me as a
quirk particular to *him*.

And I think he's the bees knees despite it.

Yes, you are probably correct about the baggage. Been reading too much of Celko, too much time
perusing the sqlserver.programming newsgroup, too much pondering the Microsoft templates for Access
2003.

Ten years of casual use of Access and following the Access newsgroups and reading Access books, and
ten years of seeing the tbl- prefix propagated upon wave after wave of new Access users. Sure, I
know the reason. Because it is just the right thing to use for Access tables. How else can a person
know whether Vendors is a table or a query?

It is just a matter of style. We can leave it at that.

 

[David W. Fenton]

Yes, you are probably correct about the baggage. Been reading too
much of Celko,

Celko is a moron. He keeps making the same arguments from theory
over and over again and is never wrong about anything. According to
himself, of course. I killfiled him a long time ago as his
contributions were always way too impractical in most cases to be
helpful. His dogmatic approach to all sorts of db issues always
struck me as completely counterproductive in real-world situations.

too much time
perusing the sqlserver.programming newsgroup,

Yet another place where a lot of people who've read a lot of books
expound on real-world programming problems without any experience
that allows them to offer real solutions.

too much pondering the Microsoft templates for Access
2003.

The Access templates are *filled* with *terrible* practices and I
recommend enormous caution in modelling any app on them without
major revisions.

Ten years of casual use of Access and following the Access
newsgroups and reading Access books, and ten years of seeing the
tbl- prefix propagated upon wave after wave of new Access users.
Sure, I know the reason. Because it is just the right thing to
use for Access tables. How else can a person know whether Vendors
is a table or a query?

That's not actually the justification I use in my own work.

It is just a matter of style. We can leave it at that.

So, in other words, you're retracting your previous assertion. Good
to know.

 

[David W. Fenton]

I always consider tables to be collections of
items and so use a plural name for a table. To access the table,
the PK points to a record (a single item), so I would use the
singular for the PK. So a table called People would have a PK of
PersonId. I tend towards to the proper English terms rather than
Entity names. Find Customer in Customers
rather than
Find pkCustomerId in tblCustomer

But that's not what anyone is advocating, so you're just disagreeing
with a strawman.

Not terribly useful.