Help with Access Database Table Structure

[billelev]

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.

tblSeries:
SeriesID (key), SeriesDataID, LineOrBar, FlipAxis
 
tblSeriesData:
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:

tblSeriesDataWeb:
SeriesDataID (key), Name, WebAddress
 
tblSeriesDataDatabase:
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:

tblSeries:
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).

tblSeries:
SeriesID (key), SeriesDataType, SeriesDataID, LineOrBar, FlipAxis
1, Web, 23, Line, True
2, Database, 45, Bar, True
 
tblSeriesDataWeb:
SeriesDataID (key), Name, WebAddress
23, "A data series from the web", "www.dataseries.com"
 
tblSeriesDataDatabase:
SeriesDataID (key), Name, DatabasePath, TableName
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.

[ChipR]

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.

[billelev]

Yep, ChipR, that does make sense. I'll try implementing that and see how it goes. Thanks.

[billelev]

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

[ChipR]

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.

[billelev]

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)?

[billelev]

This would be the resulting relationship layout...

[ChipR]

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