Best Option for Table Design

[MT DOJ Help Desk]

Access 2000

I've been working on a small flashcards database as a learning project. I
have the following 2 tables:

Side A - Contains the text for side A of the flashcard.
Side B - Contains the text for side B of the flashcard.

The two tables are related by a primary key that is of the Number data type.

I have a query that pulls data from both tables and sorts the data so that
side A of card 1 is followed by side B of card 1, then side A of card 2 is
followed by side B of card 2, etc. A form that runs the query allows the
user to flip through the records forward or backward, as if working with
actual flashcards.

I've been told that I should consider consolidating the two tables into one
table. If I do that, I'd have to add a column to store the card side (A or
B) for every record, which I currently don't need to do, so it would result
in storing more data, which seems less efficient to me. So the questions I
have are: Is one table superior to two tables? Why?

-- Tom

MT DOJ Help Desk

Making the world a safer place.

 

[Adrian Jansen]

Efficiency in terms of storage probably doesnt matter in this case. Even if
you had several million records, you would probably never notice, especially
if you coded the card 'sides' as just a 1 byte or boolean entity.

If your tables are:
SideA
ID - PK - long integer - 4 bytes
Text - n chars

SideB
ID - PK - 4 bytes
Text n chars

the alternate is one table
BothSides
ID - PK - 4 bytes
Side - boolean - 1 byte
Text n chars

then of course this table has twice as many records, but 3 less bytes per
record of storage then the two table version, for the same number of cards.
There is also storage taken by the table overhead and indexes, which must be
more for 2 tables than one, if you really want to get into details.

My preference would still be to use one table, mostly on the grounds that
you are storing one 'type' of data - the text, and normalisation rules say
that should go in one field ( and by inference in one table ).

It also makes the queries a bit easier, especially if you want to do things
like list the total contents of the cards on both sides - eg to check for
duplications etc.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[MT DOJ Help Desk]

Thanks for the information. I've been leaning toward going with one table
instead of two because I had also determined that it would make the queries
easier. I also thought about the angle that storing two tables must be less
efficient than storing one, but I didn't really know that for sure, and I
wondered if needing to store more data in one table would offset the storage
advantage of moving from two tables to one. By the way, I like your idea of
storing the "side" information as a Boolean value. I hadn't thought about
that.

Anyway, the ease of use considerations are what had mean leaning toward
going to one table. The thing is, I will need to edit a number of queries
and forms, which I didn't want to do without a fairly good reason. But now
it appears that going to one table will confer enough advantages to make it
worth the effort, and since the database currently contains only 100
records, it shouldn't be a problem getting the data consolidated down to one
table--even if I screw something up, there's not that may records to fix.

I'm actually kind of glad that I started out with two tables, even though
two tables seem unnecessary in retrospect, because I learned a few things in
setting up the two tables, relating them, and building the forms and
queries, that I would not have learned with just one table.

-- Tom

MT DOJ Help Desk

Making the world a safer place.

 

[Adrian Jansen]

Glad the advice helped your decision.

For the mechanics of converting, my usual technique is to make a second
database, and import into it from the original just the bits that dont need
( much ) changing. Then you are free to fix up the stuff you do want to
change, and keep the old version running to refer to while you do it. Once
you have the new structure correct, importing the data from the old tables
is usually just a matter of a few append queries.

A utility like Find and Replace also helps a lot in making global changes to
table and field names.
See http://www.rickworld.com

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Thanks for the information. I've been leaning toward going with one table
instead of two because I had also determined that it would make the queries
easier. I also thought about the angle that storing two tables must be less
efficient than storing one, but I didn't really know that for sure, and I
wondered if needing to store more data in one table would offset the storage
advantage of moving from two tables to one. By the way, I like your idea of
storing the "side" information as a Boolean value. I hadn't thought about
that.

.... snip ...