Professional Web Applications Themes

Sharing one auto_increment field between two tables - MySQL

Hi, Is it possible to have two tables A and B holding similar, but different data, sharing one auto_incremenet field? So if table A has a field ID, and table B has a field ID, any ID used in table A does not occur in table B, and vice versa. The only way I can think of is adding a third table administrating the ID, but that means extra work in the application using the database. -- PleegWat Remove caps to reply...

  1. #1

    Default Sharing one auto_increment field between two tables

    Hi,

    Is it possible to have two tables A and B holding similar, but different
    data, sharing one auto_incremenet field?

    So if table A has a field ID, and table B has a field ID, any ID used in
    table A does not occur in table B, and vice versa. The only way I can
    think of is adding a third table administrating the ID, but that means
    extra work in the application using the database.

    --
    PleegWat
    Remove caps to reply
    PleegWat Guest

  2. #2

    Default Re: Sharing one auto_increment field between two tables

    PleegWat wrote: 

    No, there is no way. But this seems like a strange requirement. What's
    the problem you're trying to solve?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Sharing one auto_increment field between two tables

    In article <com>, Jerry Stuckle
    says... 
    >
    > No, there is no way. But this seems like a strange requirement. What's
    > the problem you're trying to solve?[/ref]

    I am one of the developers of wow roster (www.wowroster.net), a open-
    source package that stores profiles for players in the World of Warcraft
    online game. We're currently reworking this package for additional
    functionality and to support functionality that will be introduced in
    the game's upcoming expansion pack.

    Players in the game can be member of a guild, and starting the expansion
    pack a player can also be member of one or more arena teams. Guilds and
    arena teams share the important property that they are a colleciton of
    players, so that pages that show data on one are likely to be able to
    show data on the other as well. So it would be easy to have one field
    that can reference both guilds and arena teams.

    However, we do not yet know what kind of data we will be exactly storing
    in the table that will be administrating the arena teams. Information
    like that is unlikely to be given out by the developer of the game
    before the release of the expansion pack, and delaying key decisions
    about our database format till then is simply not possible.

    So at the moment if I want to have one pool of ID's for the two, and
    need to add a table for this, the only thing that table would contain
    are the ID, name, and an identifier if it is a guild or arena team.
    Creating a seperate table for this seems unnecesary, which led me to
    wonder if there was an easier way to solve it.
    --
    PleegWat
    Remove caps to reply
    PleegWat Guest

  4. #4

    Default Re: Sharing one auto_increment field between two tables

    PleegWat wrote: 
    >>
    >>No, there is no way. But this seems like a strange requirement. What's
    >>the problem you're trying to solve?[/ref]
    >
    >
    > I am one of the developers of wow roster (www.wowroster.net), a open-
    > source package that stores profiles for players in the World of Warcraft
    > online game. We're currently reworking this package for additional
    > functionality and to support functionality that will be introduced in
    > the game's upcoming expansion pack.
    >
    > Players in the game can be member of a guild, and starting the expansion
    > pack a player can also be member of one or more arena teams. Guilds and
    > arena teams share the important property that they are a colleciton of
    > players, so that pages that show data on one are likely to be able to
    > show data on the other as well. So it would be easy to have one field
    > that can reference both guilds and arena teams.
    >
    > However, we do not yet know what kind of data we will be exactly storing
    > in the table that will be administrating the arena teams. Information
    > like that is unlikely to be given out by the developer of the game
    > before the release of the expansion pack, and delaying key decisions
    > about our database format till then is simply not possible.
    >
    > So at the moment if I want to have one pool of ID's for the two, and
    > need to add a table for this, the only thing that table would contain
    > are the ID, name, and an identifier if it is a guild or arena team.
    > Creating a seperate table for this seems unnecesary, which led me to
    > wonder if there was an easier way to solve it.[/ref]

    Probably not a good idea to have one column point to either an arena
    team or a guild.

    Personally I think I would create two sets of tables - one for arena
    teams and one for guilds. Keep them separate.

    Sure, you're likely to have a lot of duplicate columns - but that's ok.
    It means that you can reuse most of the code you write for one in the
    other one.

    But trying to do it your way is going to rapidly become awkward. What
    happens if, for instance, arena teams end up with completely different
    attributes and/or options than guilds?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Sharing one auto_increment field between two tables

    >> I am one of the developers of wow roster (www.wowroster.net), a open- 
    >
    >Probably not a good idea to have one column point to either an arena
    >team or a guild.[/ref]

    It may not be a good idea to have one column ("Patient ID") point
    to either a male patient or a female patient, but it's done all the
    time, even though some fields ("date of last pregnancy", "date of
    last prostate exam", "sperm count", etc.) apply to only one type.
     

    Consider (at least conceptually) making THREE tables:
    Common guild/arena team data
    Guild data
    Team data

    The ID falls in the common table and is used in the others to
    cross-reference the data between them, and the type (guild or arena
    team) falls in here also. The name is probably common data. Now,
    given your best guess, HOW MUCH data falls into the common table
    vs. the separate tables? You don't have all the information yet, but
    you do have (at least most) of the info for guilds. How applicable
    is that going to be to arena teams? Estimate.

    In the patient example, most of the data goes in the common table
    (both male and female patients have hearts, lungs, blood, date of
    last exam, balance due, etc.), although there is some specific data
    that might go into separate tables.

    Now, look at the volume of data you have in each table.

    - If almost all of the data is in the common table, it might be reasonable
    to re-use similar columns or put all the columns in the common table and
    leave the unused ones null, and omit the separate tables.

    - If almost all of the data is in separate tables, it might be
    reasonable to have two separate tables. In this case, assigning
    an ID (if you still want a common ID space) might be a bit more
    complex and require transactions.

    - If there's a balance of data in the common and separate tables,
    it might be reasonable to use three tables, with each entry having
    a row in the common table and one of the others.

    If there is a possibility of data shifting from the separate tables
    to the common table often, and this is going to become a nuisance,
    that's more of an argument to use a single common table.
     

    Gordon Guest

Similar Threads

  1. Replies: 5
    Last Post: June 27th, 06:35 AM
  2. How to personalise an auto_increment reference field ?
    By Baptiste Pillot in forum MySQL
    Replies: 11
    Last Post: June 16th, 03:31 PM
  3. Select, Join & field values - 2 tables
    By Justin Koivisto in forum MySQL
    Replies: 3
    Last Post: February 13th, 08:06 PM
  4. Displaying scrollable tables in text field...
    By wbahan in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: August 27th, 05:47 PM
  5. How to rename Access tables and field names?
    By Marshal in forum ASP Database
    Replies: 3
    Last Post: October 9th, 05:46 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139