473,574 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Help with Access Database Table Structure

119 New Member
I'm building a database and am a bit stumped about how to construct/link tables. I will describe the current configuration, then present the problem I am trying to solve.

Currently:

I currently have a table called tblSeries. This table holds information about a time series of data, and how that data should be displayed. It has a SeriesDataID field that will link to another table (tblSeriesData) that contains information about the name of the time series and where that time series is stored etc. It also has fields about how the data should be displayed (for example, as lines or bars, whether or not to flip the y axis etc.). The field summary is below.

Expand|Select|Wrap|Line Numbers
  1. tblSeries:
  2. SeriesID (key), SeriesDataID, LineOrBar, FlipAxis
  3.  
  4. tblSeriesData:
  5. SeriesDataID (key), Name, WebAddress
This allows me to easily switch the data for a particular Series, and also separate the definition of how the data should be displayed (tblSeries) from the definition of the data (tblSeriesData) .

The Problem

The problem I am having is that the Data Series can come from two locations; either the web or another database. If it is the web, a web address will point to the data location. If it is another database, a database path and table name will point to the data location.

I therefore have two types of SeriesData:

Expand|Select|Wrap|Line Numbers
  1. tblSeriesDataWeb:
  2. SeriesDataID (key), Name, WebAddress
  3.  
  4. tblSeriesDataDatabase:
  5. SeriesDataID (key), Name, DatabasePath, TableName
I am not sure how to relate these two SeriesData tables to the tblSeries table. My current thought is a structure like this:

Expand|Select|Wrap|Line Numbers
  1. tblSeries:
  2. SeriesID (key), SeriesDataType, SeriesDataID, LineOrBar, FlipAxis
where the new field [SeriesDataType] specifies whether it is Web or Database, and the SeriesDataID points to the correct item in the relevant SeriesData table.

The Question

This is the part that is stumping me. How do point/relate to one or another table, based on an entry in a particular record?

For example:

The first record in tblSeries below defines one set of SeriesData (id = 23) that is found in the tblSeriesDataWe b table. The second record has SeriesData that is defined in tblSeriesDataDa tabase (id = 45).

Expand|Select|Wrap|Line Numbers
  1. tblSeries:
  2. SeriesID (key), SeriesDataType, SeriesDataID, LineOrBar, FlipAxis
  3. 1, Web, 23, Line, True
  4. 2, Database, 45, Bar, True
  5.  
  6. tblSeriesDataWeb:
  7. SeriesDataID (key), Name, WebAddress
  8. 23, "A data series from the web", "www.dataseries.com"
  9.  
  10. tblSeriesDataDatabase:
  11. SeriesDataID (key), Name, DatabasePath, TableName
  12. 45, "A data series from another database", "C:/database.mdb", "tblName"
I have considered combining the two SeriesData tables, but this seems like poor design as they are essentially different objects, and combining them will create a lot of redundancy in the table.

I hope I have describe this adequately, and I would love to hear any suggestions.
Feb 11 '09 #1
7 3879
ChipR
1,287 Recognized Expert Top Contributor
There are many ways to do this, but I think I would organize the data a little differently. It seems like you have:

tblSeriesData
SeriesID (key)
Name
DataType
LineOrBar
FlipAxis
LocationType - "Web" or "Database"

tblWebLocations
SeriesID
WebAddress

tblDatabaseLoca tions
SeriesID
DatabasePath
TableName

This way when you look up a series, you can easily get it's location type and process appropriately.
Feb 12 '09 #2
billelev
119 New Member
Yep, ChipR, that does make sense. I'll try implementing that and see how it goes. Thanks.
Feb 12 '09 #3
billelev
119 New Member
I've realized a problem with the proposed solution. I should have mentioned that the WebLocations and DatabaseLocatio ns should be unique and therefore not repeated. Multiple Series can have the same DatabaseLocatio n or WebLocation. In this case, the only way the above solution would work would be to repeat the definition for the locations.

Maybe the way to explain this is to say there are well defined and unique web and database locations:

tblWebLocations
WebLocationID
WebAddress

tblDatabaseLoca tions
DataBaseLocatio nID
DatabasePath
TableName

And then there a multiple series that should reference one of the above locations:

tblSeries
SeriesID (key)
Name
DataType
LineOrBar
FlipAxis
<Something else here?>

I'm still not sure how to achieve this...

Perhaps this would work:

tblWebLocations
WebLocationID
Name
WebAddress

tblDatabaseLoca tions
DataBaseLocatio nID
Name
DatabasePath
TableName

Then a union query, combining the above tables

qryLinkLocation s
LinkLocationID (not sure how to create this in a UNION query!)
Name
LocationType (either Web or DataBase)
LocationID

And then the Series table that references the query through LinkLocationID

tblSeries
SeriesID (key)
Name
DataType
LineOrBar
FlipAxis
LinkLocationID
Feb 12 '09 #4
ChipR
1,287 Recognized Expert Top Contributor
In that case, I would lean toward:

tblSeries
SeriesID (key)
Name
DataType
LineOrBar
FlipAxis
LocationID

tblLocations
LocationID (key)
LocationType
Location - holds web address or path
TableName - null if web location

Some storage space is wasted with nulls, but that shouldn't be a major concern.
Feb 12 '09 #5
billelev
119 New Member
I could do that, but I presented simplified Location tables - there are more fields than the few I mentioned, and hence more nulls.

I guess I could just have a linking table between Series and the Locations that contained each of the unique Location IDs (rather than the query)?
Feb 12 '09 #6
billelev
119 New Member
This would be the resulting relationship layout...
Attached Images
File Type: jpg layout.jpg (15.1 KB, 3866 views)
Feb 12 '09 #7
ChipR
1,287 Recognized Expert Top Contributor
Had to chew on this over lunch. Here, I believe, is the correct solution.

tblSeriesData
SeriesID (key)
Name
DataType
LineOrBar
FlipAxis
WebLocationID (one or the other)
DatabaseLocatio nID (may be null)
(optional) LocationType - "Web" or "Database"

tblWebLocations
LocationID
WebAddress

tblDatabaseLoca tions
LocationID
DatabasePath
TableName
Feb 12 '09 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

0
2094
by: Dafella | last post by:
The following is code from Xeoport. It is suppose to access my pop3 account and load email into Mysql database. It's not inserting and there is no log or anything to tell me why. Here is the page. http://www.dafella.com/xeoport/ The original coder left no contact info. The reason I think it's the code is because of another issue.
18
12794
by: Jeremy Weiss | last post by:
I'm trying to build a database that will handle the monthly billing needs of a small company. I'm charting everything out and here's what I see: table for customers sub table to track payments received. No biggie, right? Well, here's my problem. I don't know how to tell access to modify everyone's account balance each month. And I can't...
1
9186
by: Andrew Arace | last post by:
I scoured the groups for some hands on code to perform the menial task of exporting table data from an Access 2000 database to Oracle database (in this case, it was oracle 8i but i'm assuming this will work for 9i and even 10g ) No one had what I needed, so I wrote it myself. I Rule. This code isn't going for efficiency, and isn't trying...
8
3980
by: doomx | last post by:
I'm using SQL scripts to create and alter tables in my DB I want to know if it's possible to fill the description(like in the Create table UI) using these scripts. EX: CREATE TABLE( Pk_myPrimaryKey INTEGER CONSTRAINT pk PRIMARY KEY DESCRIPTION 'This is the primary key of the table',
8
2028
by: David | last post by:
Hi, Could someone please xplain how to add a field to an existing SQL table in VB.Net I have added the field in the Server Explorer and it shows up when I reload the program but I cannot access the field from within my program. Is there something I need to refresh or do I need to recreate he Dataapdtors
4
6383
by: alexandre.brisebois | last post by:
Hi, I am using access 2003, I would like to know if there is an option to reorganize the tables in a maner that is readable, as we can do in sql sever 2000 or 2005. I have been given a database to look a and I am loosing tremendious amounts of time trying to organize it so that I could view it. Regards, Alexandre Brisebois
2
1708
by: mcmahonb | last post by:
Hello people, I'm fairly new to access and DB programming in general. I am familiar with the "basics" of MS Access, forms, tables, queries, macros, and such, but still don't have everything down pat. What I am attempting is to create a database which stores client information linked to particular reps detailing dates, financial info,...
4
12426
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is this: can Access create the document and place it as an OLE object to the relevant table? Any help is greatly appreciated. Ricky
5
2505
by: Sam | last post by:
Hi, I have one table like : MyTable {field1, field2, startdate, enddate} I want to have the count of field1 between startdate and enddate, and the count of field2 where field2 = 1 between startdate and enddate, all in the same query.
6
26295
by: provor | last post by:
Hello, I have the following code that I am using when a user presses a button to import an excel file into a table. The code is hard coded to point to the correct table. This works great for this one table. My problem is I have two buttons I want to use this code for for the two buttons would put the data in different tables. I have tried...
0
7807
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7730
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8058
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8241
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7820
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5624
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3742
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3753
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1061
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.