Are there good reasons to go against the one-to-one, one-to-many and many-to-many database archetypes? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern) The Ask Question Wizard is Live! Data science time! April 2019 and salary with experience Should we burninate the [wrap] tag?How does database indexing work?Auto Generate Database Diagram MySQLDatabase, Table and Column Naming Conventions?How do I quickly rename a MySQL database (change schema name)?How to list the tables in a SQLite database file that was opened with ATTACH?How do I connect to a MySQL Database in Python?Database development mistakes made by application developersWhat are the options for storing hierarchical data in a relational database?How to get the sizes of the tables of a MySQL database?Difference between scaling horizontally and vertically for databases
Bonus calculation: Am I making a mountain out of a molehill?
Single word antonym of "flightless"
Does the Giant Rocktopus have a Swim Speed?
Gastric acid as a weapon
How discoverable are IPv6 addresses and AAAA names by potential attackers?
Right-skewed distribution with mean equals to mode?
Did Xerox really develop the first LAN?
How can I make names more distinctive without making them longer?
What is a Meta algorithm?
3 doors, three guards, one stone
Does surprise arrest existing movement?
Why are there no cargo aircraft with "flying wing" design?
When to stop saving and start investing?
WAN encapsulation
Should I discuss the type of campaign with my players?
Why don't the Weasley twins use magic outside of school if the Trace can only find the location of spells cast?
How much radiation do nuclear physics experiments expose researchers to nowadays?
List *all* the tuples!
If a contract sometimes uses the wrong name, is it still valid?
How to assign captions for two tables in LaTeX?
How to recreate this effect in Photoshop?
Does polymorph use a PC’s CR or its level?
What are the pros and cons of Aerospike nosecones?
What would be the ideal power source for a cybernetic eye?
Are there good reasons to go against the one-to-one, one-to-many and many-to-many database archetypes?
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)
The Ask Question Wizard is Live!
Data science time! April 2019 and salary with experience
Should we burninate the [wrap] tag?How does database indexing work?Auto Generate Database Diagram MySQLDatabase, Table and Column Naming Conventions?How do I quickly rename a MySQL database (change schema name)?How to list the tables in a SQLite database file that was opened with ATTACH?How do I connect to a MySQL Database in Python?Database development mistakes made by application developersWhat are the options for storing hierarchical data in a relational database?How to get the sizes of the tables of a MySQL database?Difference between scaling horizontally and vertically for databases
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a table called 'product' with the following columns: id, name, price and tablename.
I have a group of tables named after the column tablename of the table 'product', and with the following columns: customer_id and quantity.
I wanted to join the table 'products' with the count of the rows for each associated tables.
If the tables were set up in a conventional way 2 tables called 'transaction' and 'product' in a one to many relationship, I could have done this:
SELECT product, count(*)
FROM product p
INNER JOIN transaction t on p.product_id = t.product_id
GROUP BY product
However, it's not set up this way, so I had to do this:
SELECT p.name, p.price, p.id, p.tablename, t.table_rows as Count
FROM products p
INNER JOIN
(SELECT table_name, auto_increment
FROM INFORMATION_SCHEMA.TABLES) t on t.table_name = p.tablename
which is not too bad. So is there a reason (an advantage) why using this unconventional database structure can be useful? We have some tables with 3000 transactions. I always thought the one-to-many archetypes should always be used. I don't want to ask my lead developer why he did it this way so as to not sound rude to him.
mysql database database-design
asked Mar 8 at 16:14
yocuyocu
63
add a comment |
I have a table called 'product' with the following columns: id, name, price and tablename.
I have a group of tables named after the column tablename of the table 'product', and with the following columns: customer_id and quantity.
I wanted to join the table 'products' with the count of the rows for each associated tables.
If the tables were set up in a conventional way 2 tables called 'transaction' and 'product' in a one to many relationship, I could have done this:
SELECT product, count(*)
FROM product p
INNER JOIN transaction t on p.product_id = t.product_id
GROUP BY product
However, it's not set up this way, so I had to do this:
SELECT p.name, p.price, p.id, p.tablename, t.table_rows as Count
FROM products p
INNER JOIN
(SELECT table_name, auto_increment
FROM INFORMATION_SCHEMA.TABLES) t on t.table_name = p.tablename
which is not too bad. So is there a reason (an advantage) why using this unconventional database structure can be useful? We have some tables with 3000 transactions. I always thought the one-to-many archetypes should always be used. I don't want to ask my lead developer why he did it this way so as to not sound rude to him.
mysql database database-design
asked Mar 8 at 16:14
yocuyocu
63
1
"I have a group of tables named ..." -- Do not do that!
– Rick James
Mar 13 at 21:18
add a comment |
I have a table called 'product' with the following columns: id, name, price and tablename.
I have a group of tables named after the column tablename of the table 'product', and with the following columns: customer_id and quantity.
I wanted to join the table 'products' with the count of the rows for each associated tables.
If the tables were set up in a conventional way 2 tables called 'transaction' and 'product' in a one to many relationship, I could have done this:
SELECT product, count(*)
FROM product p
INNER JOIN transaction t on p.product_id = t.product_id
GROUP BY product
However, it's not set up this way, so I had to do this:
SELECT p.name, p.price, p.id, p.tablename, t.table_rows as Count
FROM products p
INNER JOIN
(SELECT table_name, auto_increment
FROM INFORMATION_SCHEMA.TABLES) t on t.table_name = p.tablename
which is not too bad. So is there a reason (an advantage) why using this unconventional database structure can be useful? We have some tables with 3000 transactions. I always thought the one-to-many archetypes should always be used. I don't want to ask my lead developer why he did it this way so as to not sound rude to him.
mysql database database-design
asked Mar 8 at 16:14
yocuyocu
63
I have a table called 'product' with the following columns: id, name, price and tablename.
I have a group of tables named after the column tablename of the table 'product', and with the following columns: customer_id and quantity.
I wanted to join the table 'products' with the count of the rows for each associated tables.
If the tables were set up in a conventional way 2 tables called 'transaction' and 'product' in a one to many relationship, I could have done this:
SELECT product, count(*)
FROM product p
INNER JOIN transaction t on p.product_id = t.product_id
GROUP BY product
However, it's not set up this way, so I had to do this:
SELECT p.name, p.price, p.id, p.tablename, t.table_rows as Count
FROM products p
INNER JOIN
(SELECT table_name, auto_increment
FROM INFORMATION_SCHEMA.TABLES) t on t.table_name = p.tablename
which is not too bad. So is there a reason (an advantage) why using this unconventional database structure can be useful? We have some tables with 3000 transactions. I always thought the one-to-many archetypes should always be used. I don't want to ask my lead developer why he did it this way so as to not sound rude to him.
mysql database database-design
mysql database database-design
asked Mar 8 at 16:14
yocuyocu
63
asked Mar 8 at 16:14
yocuyocu
63
asked Mar 8 at 16:14
yocuyocu
63
asked Mar 8 at 16:14
yocuyocu
63
asked Mar 8 at 16:14
yocuyocu
63
63
1
"I have a group of tables named ..." -- Do not do that!
– Rick James
Mar 13 at 21:18
add a comment |
1
"I have a group of tables named ..." -- Do not do that!
– Rick James
Mar 13 at 21:18
1
1
"I have a group of tables named ..." -- Do not do that!
– Rick James
Mar 13 at 21:18
"I have a group of tables named ..." -- Do not do that!
– Rick James
Mar 13 at 21:18
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55067003%2fare-there-good-reasons-to-go-against-the-one-to-one-one-to-many-and-many-to-man%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55067003%2fare-there-good-reasons-to-go-against-the-one-to-one-one-to-many-and-many-to-man%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
"I have a group of tables named ..." -- Do not do that!
– Rick James
Mar 13 at 21:18