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 tblSeriesDataWe b table. The second record has SeriesData that is defined in tblSeriesDataDa tabase (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.
7 3894 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.
Yep, ChipR, that does make sense. I'll try implementing that and see how it goes. Thanks.
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
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.
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)?
This would be the resulting relationship layout...
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
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.
|
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 just always
assume that their monthly bill is $16 just because their balance is $16. If
I do that...
|
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 to be dynamic.
It doesn't create the table structure in Oracle, that's up to you. (I
|
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',
|
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
| |
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
|
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, addresses and such. Instead of creating a flat table with all this info inside it, controlled by forms...
|
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
|
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.
|
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 copying and changing a few things and nothing will work for me. The code is set up in a module and then I...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |