If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Best Option for Table Design
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 a Is one table superior to two tables? Why? -- Tom MT DOJ Help Desk Making the world a safer place. |
#2
|
|||
|
|||
Best Option for Table Design
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 a 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" wrote in message ... 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 a Is one table superior to two tables? Why? -- Tom MT DOJ Help Desk Making the world a safer place. |
#3
|
|||
|
|||
Best Option for Table Design
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" wrote in message ... 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 a 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" wrote in message ... 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 a Is one table superior to two tables? Why? -- Tom MT DOJ Help Desk Making the world a safer place. |
#4
|
|||
|
|||
Best Option for Table Design
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 "MT DOJ Help Desk" wrote in message ... 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 ... |
Thread Tools | |
Display Modes | |
|
|