SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Flowing Id Concept

    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....
    Please...Never describe anything to me using foo and bar.

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,863
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)
    Please...Never describe anything to me using foo and bar.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by prince_mallow View Post
    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
    why?

    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why?
    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?
    Please...Never describe anything to me using foo and bar.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by prince_mallow View Post
    This is what I want to know, why would one do this?
    that's a good question... but why do ~you~ want to do it?


    Quote Originally Posted by prince_mallow View Post
    So keeping track of which tables link to what other table would be called metadata?
    yup

    why would you want to do that, instead of letting the database itself take care of it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Please...Never describe anything to me using foo and bar.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •