Mixing
![Could I use LinkedIn to improve visibility WITHIN my company? [closed]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjbpfN9tAutmK93bJRC3ZoROZzi2TJDms5n8_qJuhgE0a9b52OOHayv3NGT8igAdFL7byXNst-_1DZK5SjrIJ28_6RQPUpBROqMs5s6jo-ZsjX8kjDwfxJufIitH3TaQRXWaGSQKRQib-f/s72-c/1.jpg)
Should the referencing table have primary key, auto increment, or index?
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
1
down vote
favorite
I'm trying to implement a list. Each list item will be associated with one or more tags. And each tag will be associated with one or more list items.
I'm still pretty new to this field. Fortunately, I managed to find a solution in this question, whose answer suggests the following database design:
Articles table
- id
- name
- etc
Relational Tag Arts table
- Fkey tags id
- Fkey article id
Tags table
- id
- name
- etc
I already created my three tables. Now what I don't understand is that, should the referencing table (the one that contains two foreign keys) have primary key, auto increment, or index?
And although I'm not 100% sure, I guess the referencing table's content is purposely "repetitive". For example, if I add a new item which is associated with 5 tags, then the referencing table should be added with 5 more records, which all have the same item id but have different tag id's. Am I correct?
database-design
asked 2 hours ago
Ian Y.
1134
add a comment |Â
up vote
1
down vote
favorite
I'm trying to implement a list. Each list item will be associated with one or more tags. And each tag will be associated with one or more list items.
I'm still pretty new to this field. Fortunately, I managed to find a solution in this question, whose answer suggests the following database design:
Articles table
- id
- name
- etc
Relational Tag Arts table
- Fkey tags id
- Fkey article id
Tags table
- id
- name
- etc
I already created my three tables. Now what I don't understand is that, should the referencing table (the one that contains two foreign keys) have primary key, auto increment, or index?
And although I'm not 100% sure, I guess the referencing table's content is purposely "repetitive". For example, if I add a new item which is associated with 5 tags, then the referencing table should be added with 5 more records, which all have the same item id but have different tag id's. Am I correct?
database-design
asked 2 hours ago
Ian Y.
1134
1
Correct, about the repetition of the article and tag ids in the many-to-many table.
– ypercubeᵀᴹ
49 mins ago
Thanks for helping out!
– Ian Y.
8 mins ago
add a comment |Â
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I'm trying to implement a list. Each list item will be associated with one or more tags. And each tag will be associated with one or more list items.
I'm still pretty new to this field. Fortunately, I managed to find a solution in this question, whose answer suggests the following database design:
Articles table
- id
- name
- etc
Relational Tag Arts table
- Fkey tags id
- Fkey article id
Tags table
- id
- name
- etc
I already created my three tables. Now what I don't understand is that, should the referencing table (the one that contains two foreign keys) have primary key, auto increment, or index?
And although I'm not 100% sure, I guess the referencing table's content is purposely "repetitive". For example, if I add a new item which is associated with 5 tags, then the referencing table should be added with 5 more records, which all have the same item id but have different tag id's. Am I correct?
database-design
asked 2 hours ago
Ian Y.
1134
I'm trying to implement a list. Each list item will be associated with one or more tags. And each tag will be associated with one or more list items.
I'm still pretty new to this field. Fortunately, I managed to find a solution in this question, whose answer suggests the following database design:
Articles table
- id
- name
- etc
Relational Tag Arts table
- Fkey tags id
- Fkey article id
Tags table
- id
- name
- etc
I already created my three tables. Now what I don't understand is that, should the referencing table (the one that contains two foreign keys) have primary key, auto increment, or index?
And although I'm not 100% sure, I guess the referencing table's content is purposely "repetitive". For example, if I add a new item which is associated with 5 tags, then the referencing table should be added with 5 more records, which all have the same item id but have different tag id's. Am I correct?
database-design
database-design
asked 2 hours ago
Ian Y.
1134
asked 2 hours ago
Ian Y.
1134
edited 2 hours ago
asked 2 hours ago
Ian Y.
1134
asked 2 hours ago
Ian Y.
1134
asked 2 hours ago
Ian Y.
1134
1134
1
Correct, about the repetition of the article and tag ids in the many-to-many table.
– ypercubeᵀᴹ
49 mins ago
Thanks for helping out!
– Ian Y.
8 mins ago
add a comment |Â
1
Correct, about the repetition of the article and tag ids in the many-to-many table.
– ypercubeᵀᴹ
49 mins ago
Thanks for helping out!
– Ian Y.
8 mins ago
1
1
Correct, about the repetition of the article and tag ids in the many-to-many table.
– ypercubeᵀᴹ
49 mins ago
Correct, about the repetition of the article and tag ids in the many-to-many table.
– ypercubeᵀᴹ
49 mins ago
Thanks for helping out!
– Ian Y.
8 mins ago
Thanks for helping out!
– Ian Y.
8 mins ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
2
down vote
You may want to consider taking one step back a think about the design process. Once you are familiar with that you can often skip it and directly implement it. I would suggest to start out with the tables (in theory called relations). You seem to have found:
* Articles
* Tags
Next step is to think about what uniquely identifies a row (tuple) in Articles and Tags. That may be a single column (attribute) or a combination of columns. What would you use to identify an article if you talked to someone about it? All such attributes or combination of attributes is known as candidate keys. In this process, you may choose to add an artificial (surrogate) key, I assume id is such an attribute. The reasons for adding surrogate keys can, for example, be that the natural keys are too complex, not stable enough, etc.
Speaking of id, I strongly suggest that you name it article_id or similar, and stick with that name through the model. Names such as Id, Name, etc are too vague to be meaningful and usually collides with reserved words in the DBMS that you use. Assume we have concluded this:
CREATE TABLE Articles
( article_id ... not null primary key -- surrogate key
, article_name ... not null
, publishing_date date not null
, ...
, primary key (article_id)
, unique ( article_name, publishing_date )
);
CREATE TABLE Tags
( tag_id ... not null primary key
, tag_name ... not null unique
);
How are these tables related? You seem to have concluded that there is an N-N relationship between an Article and a Tag and this is usually implemented with a third table (sometime named junction or link table). Let's call this table:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
);
Now for your original question, what uniquely identifies this relationship? I would suggest here a composite key like:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
, primary key (article_id, tag_id)
);
If you sometimes want to retrieve all articles for a given tag you can add an index like:
CREATE UNIQUE INDEX ... ON Article_Tags (tag_id, article_id);
The above is much simplified but may give you an idea or two on how to start thinking on the design process.
answered 51 mins ago
Lennart
12.2k21243
Thank you for such a detailed explanation! Could you please explain the purpose ofprimary keyandUNIQUE INDEXin the Article_Tags table? If I understand correctly, queries will only use either article_id or tag_id.
– Ian Y.
8 mins ago
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
You may want to consider taking one step back a think about the design process. Once you are familiar with that you can often skip it and directly implement it. I would suggest to start out with the tables (in theory called relations). You seem to have found:
* Articles
* Tags
Next step is to think about what uniquely identifies a row (tuple) in Articles and Tags. That may be a single column (attribute) or a combination of columns. What would you use to identify an article if you talked to someone about it? All such attributes or combination of attributes is known as candidate keys. In this process, you may choose to add an artificial (surrogate) key, I assume id is such an attribute. The reasons for adding surrogate keys can, for example, be that the natural keys are too complex, not stable enough, etc.
Speaking of id, I strongly suggest that you name it article_id or similar, and stick with that name through the model. Names such as Id, Name, etc are too vague to be meaningful and usually collides with reserved words in the DBMS that you use. Assume we have concluded this:
CREATE TABLE Articles
( article_id ... not null primary key -- surrogate key
, article_name ... not null
, publishing_date date not null
, ...
, primary key (article_id)
, unique ( article_name, publishing_date )
);
CREATE TABLE Tags
( tag_id ... not null primary key
, tag_name ... not null unique
);
How are these tables related? You seem to have concluded that there is an N-N relationship between an Article and a Tag and this is usually implemented with a third table (sometime named junction or link table). Let's call this table:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
);
Now for your original question, what uniquely identifies this relationship? I would suggest here a composite key like:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
, primary key (article_id, tag_id)
);
If you sometimes want to retrieve all articles for a given tag you can add an index like:
CREATE UNIQUE INDEX ... ON Article_Tags (tag_id, article_id);
The above is much simplified but may give you an idea or two on how to start thinking on the design process.
answered 51 mins ago
Lennart
12.2k21243
Thank you for such a detailed explanation! Could you please explain the purpose ofprimary keyandUNIQUE INDEXin the Article_Tags table? If I understand correctly, queries will only use either article_id or tag_id.
– Ian Y.
8 mins ago
add a comment |Â
up vote
2
down vote
You may want to consider taking one step back a think about the design process. Once you are familiar with that you can often skip it and directly implement it. I would suggest to start out with the tables (in theory called relations). You seem to have found:
* Articles
* Tags
Next step is to think about what uniquely identifies a row (tuple) in Articles and Tags. That may be a single column (attribute) or a combination of columns. What would you use to identify an article if you talked to someone about it? All such attributes or combination of attributes is known as candidate keys. In this process, you may choose to add an artificial (surrogate) key, I assume id is such an attribute. The reasons for adding surrogate keys can, for example, be that the natural keys are too complex, not stable enough, etc.
Speaking of id, I strongly suggest that you name it article_id or similar, and stick with that name through the model. Names such as Id, Name, etc are too vague to be meaningful and usually collides with reserved words in the DBMS that you use. Assume we have concluded this:
CREATE TABLE Articles
( article_id ... not null primary key -- surrogate key
, article_name ... not null
, publishing_date date not null
, ...
, primary key (article_id)
, unique ( article_name, publishing_date )
);
CREATE TABLE Tags
( tag_id ... not null primary key
, tag_name ... not null unique
);
How are these tables related? You seem to have concluded that there is an N-N relationship between an Article and a Tag and this is usually implemented with a third table (sometime named junction or link table). Let's call this table:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
);
Now for your original question, what uniquely identifies this relationship? I would suggest here a composite key like:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
, primary key (article_id, tag_id)
);
If you sometimes want to retrieve all articles for a given tag you can add an index like:
CREATE UNIQUE INDEX ... ON Article_Tags (tag_id, article_id);
The above is much simplified but may give you an idea or two on how to start thinking on the design process.
answered 51 mins ago
Lennart
12.2k21243
Thank you for such a detailed explanation! Could you please explain the purpose ofprimary keyandUNIQUE INDEXin the Article_Tags table? If I understand correctly, queries will only use either article_id or tag_id.
– Ian Y.
8 mins ago
add a comment |Â
up vote
2
down vote
up vote
2
down vote
You may want to consider taking one step back a think about the design process. Once you are familiar with that you can often skip it and directly implement it. I would suggest to start out with the tables (in theory called relations). You seem to have found:
* Articles
* Tags
Next step is to think about what uniquely identifies a row (tuple) in Articles and Tags. That may be a single column (attribute) or a combination of columns. What would you use to identify an article if you talked to someone about it? All such attributes or combination of attributes is known as candidate keys. In this process, you may choose to add an artificial (surrogate) key, I assume id is such an attribute. The reasons for adding surrogate keys can, for example, be that the natural keys are too complex, not stable enough, etc.
Speaking of id, I strongly suggest that you name it article_id or similar, and stick with that name through the model. Names such as Id, Name, etc are too vague to be meaningful and usually collides with reserved words in the DBMS that you use. Assume we have concluded this:
CREATE TABLE Articles
( article_id ... not null primary key -- surrogate key
, article_name ... not null
, publishing_date date not null
, ...
, primary key (article_id)
, unique ( article_name, publishing_date )
);
CREATE TABLE Tags
( tag_id ... not null primary key
, tag_name ... not null unique
);
How are these tables related? You seem to have concluded that there is an N-N relationship between an Article and a Tag and this is usually implemented with a third table (sometime named junction or link table). Let's call this table:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
);
Now for your original question, what uniquely identifies this relationship? I would suggest here a composite key like:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
, primary key (article_id, tag_id)
);
If you sometimes want to retrieve all articles for a given tag you can add an index like:
CREATE UNIQUE INDEX ... ON Article_Tags (tag_id, article_id);
The above is much simplified but may give you an idea or two on how to start thinking on the design process.
answered 51 mins ago
Lennart
12.2k21243
You may want to consider taking one step back a think about the design process. Once you are familiar with that you can often skip it and directly implement it. I would suggest to start out with the tables (in theory called relations). You seem to have found:
* Articles
* Tags
Next step is to think about what uniquely identifies a row (tuple) in Articles and Tags. That may be a single column (attribute) or a combination of columns. What would you use to identify an article if you talked to someone about it? All such attributes or combination of attributes is known as candidate keys. In this process, you may choose to add an artificial (surrogate) key, I assume id is such an attribute. The reasons for adding surrogate keys can, for example, be that the natural keys are too complex, not stable enough, etc.
Speaking of id, I strongly suggest that you name it article_id or similar, and stick with that name through the model. Names such as Id, Name, etc are too vague to be meaningful and usually collides with reserved words in the DBMS that you use. Assume we have concluded this:
CREATE TABLE Articles
( article_id ... not null primary key -- surrogate key
, article_name ... not null
, publishing_date date not null
, ...
, primary key (article_id)
, unique ( article_name, publishing_date )
);
CREATE TABLE Tags
( tag_id ... not null primary key
, tag_name ... not null unique
);
How are these tables related? You seem to have concluded that there is an N-N relationship between an Article and a Tag and this is usually implemented with a third table (sometime named junction or link table). Let's call this table:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
);
Now for your original question, what uniquely identifies this relationship? I would suggest here a composite key like:
CREATE TABLE Article_Tags
( article_id ... not null references Articles (article_id)
, tag_id ... not null references Tags (tag_id)
, primary key (article_id, tag_id)
);
If you sometimes want to retrieve all articles for a given tag you can add an index like:
CREATE UNIQUE INDEX ... ON Article_Tags (tag_id, article_id);
The above is much simplified but may give you an idea or two on how to start thinking on the design process.
answered 51 mins ago
Lennart
12.2k21243
answered 51 mins ago
Lennart
12.2k21243
answered 51 mins ago
Lennart
12.2k21243
answered 51 mins ago
Lennart
12.2k21243
12.2k21243
Thank you for such a detailed explanation! Could you please explain the purpose ofprimary keyandUNIQUE INDEXin the Article_Tags table? If I understand correctly, queries will only use either article_id or tag_id.
– Ian Y.
8 mins ago
add a comment |Â
Thank you for such a detailed explanation! Could you please explain the purpose ofprimary keyandUNIQUE INDEXin the Article_Tags table? If I understand correctly, queries will only use either article_id or tag_id.
– Ian Y.
8 mins ago
Thank you for such a detailed explanation! Could you please explain the purpose of
primary key and UNIQUE INDEX in the Article_Tags table? If I understand correctly, queries will only use either article_id or tag_id.– Ian Y.
8 mins ago
Thank you for such a detailed explanation! Could you please explain the purpose of
primary key and UNIQUE INDEX in the Article_Tags table? If I understand correctly, queries will only use either article_id or tag_id.– Ian Y.
8 mins ago
add a comment |Â
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221700%2fshould-the-referencing-table-have-primary-key-auto-increment-or-index%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
1
Correct, about the repetition of the article and tag ids in the many-to-many table.
– ypercubeᵀᴹ
49 mins ago
Thanks for helping out!
– Ian Y.
8 mins ago