I have a table named urls
:
id | name |
---|---|
1 | url1 |
2 | url2 |
This urls
table is referenced by two other tables documents
and doctypes
.
documents
:
id | reference |
---|---|
1 | kl/dkj/192 |
2 | kl/dji/982 |
doctypes
:
id | name |
---|---|
1 | document1 |
2 | document2 |
documents
and doctypes
have other columns also. I want to add urls for both documents
and doctypes
table. My requirement is to know how can I establish a relationship between urls
and these tables. I think when I list document
and doctypes
in my app, I don’t want to display urls, but when I click on detailed view, I can join document
and urls
tables for knowing url value.
Now I designed relationship like this – document_urls
:
document_id | url_id |
---|---|
1 | 3 |
2 | 2 |
doctype_urls
:
doctype_id | url_id |
---|---|
1 | 3 |
2 | 2 |
My question: is this a good approach?
I also think I could have altered documents table to reference urls table like this
id | reference | url_id |
---|---|---|
1 | kl/djkf/34 | 1 |
2 | kl/de/3445 | 3 |
For this approach my question: is it a good approach, since url
column is not a required column, and I was not listing url details when I list my documents.
2
Answers
The easiest solution would be to put the urls in the tables:
You can even add unique constraints on the urls:
The only thing that this does not do is guarantee that there is no url used both for a doctype and a document at the same time. If you must guarantee that, this becomes much more complicated:
The only way to determine the best design for a database schema is to analyse the entities and relationships you want to represent.
Firstly, be careful of confusing data types with entities – just because many things have an attribute called "URL", with similar-looking content, does not necessarily mean that they should reference a URL entity. As an example, imagine having a "users" table and a "products" table; both might have a "name" attribute, but it would be very unusual to consider "name" an entity on its own, and have a single table called "names", because "user name" and "product name" don’t have any natural connection.
If the "document URL" is actually always added to a fixed prefix of "/documents/", and a "doctype URL" is always added to a fixed prefix of "/doctypes/", they can be represented as separate entities. If you want to represent a single pool of URLs, which can be assigned ad hoc for various different purposes, you need a separate "URL" entity.
Secondly, you need to determine the "cardinality" of the relationships. Can one document have zero URLs, multiple URLs, or always exactly one? Can one URL refer to zero documents, multiple documents, or always exactly one?
With the answers to these questions in mind, you can choose between a number of schemas:
NOT (document_id IS NULL AND doctype_id IS NULL)
(reword with care, remembering De Morgan’s laws!)