Bitcoin Forum
December 04, 2016, 10:33:05 PM *
News: Latest stable version of Bitcoin Core: 0.13.1  [Torrent].
 
   Home   Help Search Donate Login Register  
Pages: [1]
  Print  
Author Topic: MySQL table pointers  (Read 2955 times)
genjix
Legendary
*
Offline Offline

Activity: 1232


View Profile
April 22, 2011, 04:12:27 PM
 #1

Is there a way to have a field in MySQL that acts as a pointer to another table?

I want to have a bunch of different records with different fields but sharing some common ones.

common field A | common field B | pointer

then I could do:

SELECT mytbl ... JOIN POINTER(mytbl.pointer) ...;

Or do I need to have a VARCHAR(6) ident field and simply have a switch... case in my application that joins depending on which "other" table is needed?
1480890785
Hero Member
*
Offline Offline

Posts: 1480890785

View Profile Personal Message (Offline)

Ignore
1480890785
Reply with quote  #2

1480890785
Report to moderator
1480890785
Hero Member
*
Offline Offline

Posts: 1480890785

View Profile Personal Message (Offline)

Ignore
1480890785
Reply with quote  #2

1480890785
Report to moderator
1480890785
Hero Member
*
Offline Offline

Posts: 1480890785

View Profile Personal Message (Offline)

Ignore
1480890785
Reply with quote  #2

1480890785
Report to moderator
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1480890785
Hero Member
*
Offline Offline

Posts: 1480890785

View Profile Personal Message (Offline)

Ignore
1480890785
Reply with quote  #2

1480890785
Report to moderator
1480890785
Hero Member
*
Offline Offline

Posts: 1480890785

View Profile Personal Message (Offline)

Ignore
1480890785
Reply with quote  #2

1480890785
Report to moderator
error
Hero Member
*****
Offline Offline

Activity: 574



View Profile
April 23, 2011, 02:35:32 AM
 #2

Sounds like something's terribly wrong with your table design. Can you provide some more details of what it is you're trying to accomplish?

15UFyv6kfWgq83Pp3yhXPr8rknv9m6581W
Joshi33xx
Newbie
*
Offline Offline

Activity: 5


View Profile
April 23, 2011, 10:22:03 AM
 #3

Look into creating a "view".

http://en.wikipedia.org/wiki/View_(database)

That might let you do what you need. The "default" is then whatever data you define it to be in the view.

You can have as many views as you like.
genjix
Legendary
*
Offline Offline

Activity: 1232


View Profile
April 23, 2011, 10:46:18 AM
 #4

For Britcoin there are 3 different types of withdrawal: Bitcoin, UK-domestic, UK-international:

Code:
mysql> describe requests;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| reqid     | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| req_type  | varchar(6)          | NO   |     | NULL              |                |
| uid       | int(10) unsigned    | NO   |     | NULL              |                |
| amount    | bigint(20) unsigned | NO   |     | NULL              |                |
| curr_type | varchar(6)          | NO   |     | NULL              |                |
| timest    | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| status    | varchar(6)          | NO   |     | VERIFY            |                |
+-----------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

mysql> describe bitcoin_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| addy  | varchar(44)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe international_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| iban  | varchar(36)      | NO   |     | NULL    |       |
| swift | varchar(12)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe uk_requests;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| reqid     | int(10) unsigned | NO   | PRI | NULL    |       |
| name      | varchar(40)      | NO   |     | NULL    |       |
| bank      | varchar(40)      | NO   |     | NULL    |       |
| acc_num   | varchar(8)       | NO   |     | NULL    |       |
| sort_code | varchar(6)       | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Each of those requests 'shares' the requests tables but adds their own specific data.

I'd like some way to tell which data should be joined to the requests tables and what type of request it was (Bitcoin, UK-domestic, UK-international).

Maybe an accounts field or using views is the way to go.
Alex Beckenham
Full Member
***
Offline Offline

Activity: 154


View Profile
April 23, 2011, 11:12:24 AM
 #5

Your schema looks fine; Probably how I would have set it up, but I'm not sure what you want to query from it.

Can you write in plain English what you want to retrieve from the db and I can translate it to sql for you?

Here's an example of grabbing some particular UK request:

Code:
SELECT * FROM requests,uk_requests WHERE (requests.reqid=uk_requests.reqid) AND (uk_requests.acc_num='3');

genjix
Legendary
*
Offline Offline

Activity: 1232


View Profile
April 23, 2011, 11:34:42 AM
 #6

Well your query does a cartesian join then filters out some rows using a WHERE and is less efficient than JOIN ... ON ...  Grin

So how could I tell whether reqid='133' is from either Bitcoin, international_requests or uk_requests in a single query? I know I could do SELECT 1 FROM uk_requests WHERE reqid='133'; to see whether it exists in uk_requests, but how about for all 3 in one go?

Is an extra field in requests the way to go?
Nefario
Hero Member
*****
Offline Offline

Activity: 602


GLBSE Support support@glbse.com


View Profile WWW
April 23, 2011, 02:06:28 PM
 #7

For Britcoin there are 3 different types of withdrawal: Bitcoin, UK-domestic, UK-international:

Code:
mysql> describe requests;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| reqid     | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| req_type  | varchar(6)          | NO   |     | NULL              |                |
| uid       | int(10) unsigned    | NO   |     | NULL              |                |
| amount    | bigint(20) unsigned | NO   |     | NULL              |                |
| curr_type | varchar(6)          | NO   |     | NULL              |                |
| timest    | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| status    | varchar(6)          | NO   |     | VERIFY            |                |
+-----------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

mysql> describe bitcoin_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| addy  | varchar(44)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe international_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| iban  | varchar(36)      | NO   |     | NULL    |       |
| swift | varchar(12)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe uk_requests;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| reqid     | int(10) unsigned | NO   | PRI | NULL    |       |
| name      | varchar(40)      | NO   |     | NULL    |       |
| bank      | varchar(40)      | NO   |     | NULL    |       |
| acc_num   | varchar(8)       | NO   |     | NULL    |       |
| sort_code | varchar(6)       | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

.


Each of those requests 'shares' the requests tables but adds their own specific data.

I'd like some way to tell which data should be joined to the requests tables and what type of request it was (Bitcoin, UK-domestic, UK-international).

Maybe an accounts field or using views is the way to go.


In your three tables for bitcoin, brit, & international just add a field for the primary key of your fourth table that they are all trying to reference, say the table is called transactions, and the primary key is an auto increment integer called id.In the other tables add the field transaction_id as an integer. They should all be able to reference the common table now

PGP key id at pgp.mit.edu 0xA68F4B7C

To get help and support for GLBSE please email support@glbse.com
Alex Beckenham
Full Member
***
Offline Offline

Activity: 154


View Profile
April 23, 2011, 02:48:29 PM
 #8

Well your query does a cartesian join then filters out some rows using a WHERE and is less efficient than JOIN ... ON ...  Grin

So how could I tell whether reqid='133' is from either Bitcoin, international_requests or uk_requests in a single query? I know I could do SELECT 1 FROM uk_requests WHERE reqid='133'; to see whether it exists in uk_requests, but how about for all 3 in one go?

Is an extra field in requests the way to go?

Well, I'm sure the following is probably non-working utter crap, but it might give you ideas none-the-less...

Code:
SELECT bitcoin_requests.reqid='133' AS is_bitcoin, international_requests.reqid='133' AS is_international, uk_requests.reqid='133' AS is_uk
FROM bitcoin_requests,international_requests,uk_requests
WHERE (bitcoin_requests.reqid='133')OR(international_requests.reqid='133')OR(uk_requests.reqid='133');

Sorry can't be any help.

genjix
Legendary
*
Offline Offline

Activity: 1232


View Profile
April 25, 2011, 12:51:21 AM
 #9

Thanks.
Pages: [1]
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!