I am trying to create a bibliographic database, working with MySQL and Python, and I have a MySQL based question.
I understand how to link two tables together on a row by row basis using a scheme like this. However, references have a bit more complex structure. On any one reference you might have, say more than one author, and each author might contribute to more than one reference.
I could just have a table with rows for each author/work pairing, but that would make a messy table very quicky, and probably be a bear to query. What I would rather is some sleek way to allow for a ref_table that has a column for authors, with multiple authors listed by id, then a author_table with their id, name, and possibly a reverse reference to all works they authored.
I guess I could do this with some complex string parsing in Python, but it would seem to involve sending the entire author_table over the sql connection every time I wanted to update the ref_table. I was wondering if there were an easier more elegant way to do this within the MySQL server itself. Any ideas? Thanks.
7 1481 Atli 5,058
Recognized Expert Expert
Hi KevHill, and welcome to TSDN.
You should never put more than one piece of data in a single field, and you should never put more than one column with the same data per table.
These are the first two of three rules of database normalization.
You can read more about Database Normalisation and Table structures in this article
Anyways, if I am understanding you correctly, you will need to create a table that links your 'Author' table with your 'Refrence' table.
This is a standard way to create a N/M (many to many) relationship between tables, and is used in most, if not all, normalized relational databases.
This is how I would (and have) designed such a database.
Lets say I have this user table: -
CREATE TABLE tblUser
-
(
-
UserID BIGINT UNSIGNED AUTO_INCREMENT,
-
UserName VARCHAR(255) NOT NULL,
-
PRIMARY KEY(UserID)
-
)
-
And this article table: -
CREATE TABLE tblArticle
-
(
-
ArticleID BIGINT UNSIGNED AUTO_INCREMENT,
-
ArticleName VARCHAR(255) NOT NULL,
-
ArticleText LONGTEXT,
-
PRIMARY KEY(ArticleID)
-
)
-
To be able to link each user to many articles, and each article to many users I will have to create a table that links the users and the articles: -
CREATE TABLE tblUserArticle
-
(
-
UserID BIGINT UNSIGNED REFERENCES tblUser(UserID),
-
ArticleID BIGINT UNSIGNED REFERENCES tblArticle(ArticleID),
-
PRIMARY KEY(UserID, ArticleID)
-
)
-
Using both the UserID and the ArticleID as the primary key prevents duplicate rows.
Now, if you want to see all articles that any user has contributed to, you could do something like this: -
SELECT a.ArticleName FROM tblArticle AS a
-
INNER JOIN tblUserArticle AS ua
-
ON ua.ArticleID = a.ArticleID
-
INNER JOIN tblUser AS u
-
ON u.UserID = ua.UserID
-
WHERE u.UserID = 1;
-
Let us know if you have any questions or problems we can help with.
It seems odd to me that that is the most efficient way to code that.
Imagine if I had 100 references, and each article had an average of 4 authors. This would lead to 400 unique author/article pairings
A very common job of a reference manager would be to sort all of the articles by authors, which requires finding out all the authors on a given article. Such info should always be available, but that would require searching through 100*400=40,000 tuples every time to refresh the database. If it was 300 articles with an average of 6 authors per article (which is also not unreasonable for scientific articles used for a thesis project), that mushrooms up to 270,000 tuple searches just to refresh a UI.
Is mysql really that fast? Fast enough that the process would be faster than a python loop which just took out a string something like "id_1, id_2, id_3" and then parsed that string and performed searches on just the pertinent author tuples, which I think would be processing 400 tuples in the first example above.
Do INNER JOINs somehow get around this inefficiency?
I can see how it would be easier to search for all articles a single author had written, as you showed. If you just had many-authors-per-article, that would involve sending a relatively large amount of data over the connection compared to the 1NF standard, but that could be solved by a reverse many-articles-per-author column in the author's table. Keeping the two paired columns updated would be a bit of extra effort, but still seems to me more efficient...
So, is there something I'm missing?
MMcCarthy 14,534
Recognized Expert Moderator MVP
Hi Kevin,
The way it works is this. tblAuthor and tblArticle have a many to many relationship. By adding tblAuthorPerArticle they each have a one to many relationship with the new table. tblAuthor
AuthorID (Primary key, Autonumber)
AuthorName tblArticle
ArticleID (Primary key, Autonumber)
ArticleName tblAuthorPerArticle
AuthorID (Composite Primary key, Foreign key referencing tblAuthor)
ArticleID (Composite Primary key, Foreign key referencing tblArticle)
Query to get all articles by author lets assume we have a form (frmSeach) with a listbox (lstAuthors) based on the tblAuthor table. The AuthorName field displayed and the AuthorID field bound. -
SELECT ArticleName
-
FROM tblAuthorPerArticle INNER JOIN tblArticle
-
ON tblAuthorPerArticle.ArticleID = tblArticle.ArticleID
-
WHERE tblAuthorPerArticle.AuthorID = [Forms]![frmSearch]![lstAuthors]
-
Query to get all authors of an article we use the same form (frmSeach) with a listbox (lstArticles) based on the tblArticle table. The ArticleName field displayed and the ArticleID field bound. -
SELECT AuthorName
-
FROM tblAuthorPerArticle INNER JOIN tblAuthor
-
ON tblAuthorPerArticle.AuthorID = tblArticle.AuthorID
-
WHERE tblAuthorPerArticle.ArticleID = [Forms]![frmSearch]![lstArticles]
-
To add/Edit an record you would have a main form (frmArticle) based on tblArticle and a subform based on the following query and bound to the main form based on ArticleID. -
SELECT tblAuthorPerArticle.ArticleID, tblAuthor.*
-
FROM tblAuthorPerArticle INNER JOIN tblAuthor
-
ON tblAuthorPerArticle.AuthorID = tblAuthor.AuthorID
-
This is actually the most efficient way of handling the data. If you create your queries as saved queries and reference them by name then they will be compiled once by the Jet Engine and should work efficiently thereafter.
Mary
It'll take me awhile to get my newbie head wrapped around that one, but thanks!
MMcCarthy 14,534
Recognized Expert Moderator MVP
It'll take me awhile to get my newbie head wrapped around that one, but thanks!
Not a problem, you're welcome.
MMcCarthy 14,534
Recognized Expert Moderator MVP
Sorry, just looked at this again. Ignore my references to listboxes and forms. I didn't realise this question was in the MySQL forum. Too used to posting in the Access forum.
The structure is sound and the sql syntax is correct just not the reference to form controls.
Atli 5,058
Recognized Expert Expert
Yea, I also just realized that in my example I didn't really need to put a JOIN to the authors table, as I didn't acctually use any data from there.
It would be usefull, however, if you were to search for a Author name.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: AdamsBlueGuitar |
last post by:
Here's the situation. I have a page with 2 tables on it. In table
one, are several links. The I want the content of the links to show
up in table two. I think this can be done by nameing table...
|
by: Pentti |
last post by:
Can anyone help to understand why re-parsing occurs on a remote database
(using database links), even though we are using a prepared statement on
the local database:
Scenario:
========
We...
|
by: Chris via AccessMonster.com |
last post by:
I have done something like this a million years ago my developer skills have diminished to nothing but now I am at a new job trying to regroup what I lost. This one came across my desk recently.
I...
|
by: ET |
last post by:
I don't know whats the problem, but after I added functions
to first verify, then relink linked tables if not found,
now I can't convert that database to MDE format.
I can split the database, but...
|
by: Patrick Olurotimi Ige |
last post by:
I have a simple Stored Procedure with multiple select statements..doing
select * from links for example.
I created a DataTable and then fill the tables
But the first dtTemplate DataTable doesn't...
| |
by: Olaf Rabbachin |
last post by:
Hi folks,
I have a table in an Access-DB that has a date-field (date + time). I'd
like to use a DataSet + DataGrid to achieve a grouped/hierarchical display
where the user would work his/her way...
|
by: chrisspencer02 |
last post by:
I am looking for a method to extract the links embedded within the
Javascript in a web page: an ActiveX component, or example code in
C++/Pascal/etc. I am looking for a general solution, not one...
|
by: aaron.m.johnson |
last post by:
I have an application which contains an Access database with linked
tables that point to another database within the application. The
problem I have is that when the user installs the application,...
|
by: Salad |
last post by:
If one distributes a front/backend app is there a good practice for
refreshing the links?
I was thinking that the first time the user enters the app, it could
check for any tables. If the...
|
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,...
|
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...
| |
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,...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
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...
|
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...
|
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 ...
| |
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...
| |