prince_mallow — 2010-03-07T19:39:37-05:00 — #1
Hi, I'm looking at different ways I can set up my tables in my databases. I know I'll probably end up with around 16 tables that are all complicatedly linked to one another and I'm looking at a potential different way of using primary keys. I'm thinking of having the keys flow from one table to another in the sense that if table 1's primary key has 1,2,3,4 and it links each of these keys to table two's keys, table two's primary keys would instead of also having 1,2,3,4 would continue on the numeration and therefore be 5,6,7,8
I think for this type of organization I would also need a table that keeps track of only the primary key such as
Primary Key Table
id | table
1 | table1
2 | table1
3 | table1
4 | table1
5 | table2
6 | table2
7 | table2
8 | table2
Does anybody have any examples of a database that work like this and how this may or may not be useful. For such a thing to work I think the sql would have to read from the table of primary keys in order to find out which key is next available.
Thoughts, suggestions, approvals, or dismissals please....
felgall — 2010-03-07T20:16:31-05:00 — #2
Why not work out which actual field in each table is unique and use that for the key (or combinations of fields if appropriate). There are very few situations where a meaningless numbered field needs to be added to any table in order to give it a unique key.
prince_mallow — 2010-03-07T20:30:28-05:00 — #3
I wish I could however the data I am collecting doesn't have a set uniqueness that puts them apart from another. Things can be the same in one field and different in another and vice versa, it's really a case by case, so serialization was my way around that and before I commit I'm playing around with conceptualization. If I were to make a unique table as I proposed I could essentially also technically define the relationship of all the tables in that table (which would make the sql super complicated maybe?)
id | table | tab_1 | tab_2
1 | table1 | 0 | 5
2 | table1 | 0 | 5
3 | table1 | 0 | 6
4 | table1 | 0 | 6
5 | table2 | 0 | 0
6 | table2 | 0 | 0
but if I were to define all the relationships in one table (thereby re leaving individual tables of the multiple columns it would take. then joining the tables later on would require a query of the primary key table's columns and then a join of the results with the other two tables
(instead of linking two tables together directly I would have to go an extra step and to to a third table)
with 16 tables so far a good number of tables would link back to the first table, I'm wondering if it's a good trade off or not.
(I'm not even sure If other people can understand what I'm describing lol)
r937 — 2010-03-07T20:44:13-05:00 — #4
what is this scheme going to accomplish for you? i mean, what's the purpose?
tip: any time you need to create a table to keep track of stuff in other tables (this is called metadata) there's a strong chance that the design is suboptimal
prince_mallow — 2010-03-07T21:57:02-05:00 — #5
This is what I want to know, why would one do this?
So keeping track of which tables link to what other table would be called metadata?
r937 — 2010-03-07T22:00:48-05:00 — #6
that's a good question... but why do ~you~ want to do it?
why would you want to do that, instead of letting the database itself take care of it?
prince_mallow — 2010-03-07T22:16:32-05:00 — #7
I want to do it to have "universally Unique identifiers" (that is, if I ) quoted the right word) Maybe that way nothing can ever get the same number but then again the metadata table would hold all these values if I chose to use both methods at the same time.
r937 — 2010-03-08T02:14:23-05:00 — #8
ah, universally unique identifiers, i understand now
you can do this rather easily in oracle and postgresql, where it's called a sequence or serial number
however, there's nothing in particular to be gained from trying to manage this yourself in a database which has numeric keys that are unique only to single tables, such as identity columns in mcrosoft sql server and auto_increments in mysql
there's really no need to do it, not if you implement foreign keys properly