By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,704 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,704 IT Pros & Developers. It's quick & easy.

Help with Access Database Table Structure

100+
P: 119
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 tblSeriesDataWeb table. The second record has SeriesData that is defined in tblSeriesDataDatabase (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
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,287
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

tblDatabaseLocations
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

100+
P: 119
Yep, ChipR, that does make sense. I'll try implementing that and see how it goes. Thanks.
Feb 12 '09 #3

100+
P: 119
I've realized a problem with the proposed solution. I should have mentioned that the WebLocations and DatabaseLocations should be unique and therefore not repeated. Multiple Series can have the same DatabaseLocation 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

tblDatabaseLocations
DataBaseLocationID
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

tblDatabaseLocations
DataBaseLocationID
Name
DatabasePath
TableName

Then a union query, combining the above tables

qryLinkLocations
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

Expert 100+
P: 1,287
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

100+
P: 119
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

100+
P: 119
This would be the resulting relationship layout...
Attached Images
File Type: jpg layout.jpg (15.1 KB, 3252 views)
Feb 12 '09 #7

Expert 100+
P: 1,287
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)
DatabaseLocationID (may be null)
(optional) LocationType - "Web" or "Database"

tblWebLocations
LocationID
WebAddress

tblDatabaseLocations
LocationID
DatabasePath
TableName
Feb 12 '09 #8

Post your reply

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