#1
  1. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10

    Relational tables


    On my site I've created something that mimics relative data between tables. Looking at the link below, it shows a record for a beer, and some data at the bottom that is relative to that beer.

    KillGorack A site for nothing really..

    On clicking the link for its brewery that record again will show all resulting beers for that brewery.

    My question is, with access I know how to make a relative database, but with asp classic, I can make it look relational but I use a separate table to connect them. Is there relational functionality within the MsSQL my little admin that can do this easier? Currently every time I add to the database I have to insert records to connect data.

    I know about primary and foriegin keys. Just looking for a easier way.
    Last edited by KillGorack; January 1st, 2013 at 06:12 AM.
  2. #2
  3. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    How you inserting records into database? Foreign keys just help you keep data in sink it will not let you insert related records if primary does not exist, but it will not insert any records by yourself...
    You should create interface which will do that or on SQL server create stored procedure to manage all that and call that stored procedure every time you need to insert record
    GK
    __________________________________________________ _____
    if you found this post is useful click Give Rep button (bottom side on this reply ) and agree
  4. #3
  5. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10
    My issue is that I have multiple tables in the database (just to play with) and I want the ability to have any record in any table relative to any other record in any other table if that makes sense. I don't want to make a bunch of fields in each table to make the foreign keys.

    What I have now is ONE table with 5 Fields

    ID
    Table
    TableID
    RelativeTable
    RelativeTableID

    AS you can immagine at the end of a page where I show a record I scan the table for a match and pull up relative records.

    See KillGorack A site for nothing really..

    Just getting the data into that separate table is a little awkward. Is there best practices of how to do something similar with classic ASP? (without saying switch to .net of course)

    Oh to answer your question inserting data into that linking table is manual. I have a web form..
    killgorack.com
  6. #4
  7. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    I don't really follow your problem.

    You have a table of breweries.

    Code:
    breweryid	breweryname
    1		Ranier
    2		Goetz
    3		Heim
    4		Redhook
    You have a table of beers.
    Code:
    beerid	beername		breweryid
    1	Ranier			1
    2	Country Club		2
    3	Bailey's Special Picnic	2
    4	Our Pride		3
    5	Blackhook		4
    6	ECS			4
    Redhook gets a new beer you insert a 7 for a beer id, "Longhammer" as a name, and 4 for a brewery.
    Boom, simple, you are done.

    You obviously know what you are doing and you have a nice site, so I think maybe you are just not making yourself clear as to what you want.

    "Inserting records to connect data" makes little sense to me. ASP is just the server side language being used to construct the HTML presentation- it doesn't have a anything to do with the data structure. MSSQL can do anything and more than Access,

    "any record in any table relative to any other record in any other table if that makes sense." Sorry, but it doesn't.

    What do you want?
    Brinkster- free development account. Not affiliated, but I sure like free. Brinkster.com
  8. #5
  9. Contributing User
    ASP Skiller (1500 - 1999 posts)

    Join Date
    Mar 2005
    Location
    Columbus, OH
    Posts
    1,608
    Rep Power
    278
    Absolutely agreed with bigmike. If you expect from as help please describe your problem as clear as you can. Other wise we can not help you.
  10. #6
  11. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10
    Getting closer, here's a video. Just gotta get input a little easier. Now I currently have to remember / write down the ID's the vid explains better than I can here.

    http://youtu.be/wI-GM2ZMsMU
  12. #7
  13. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    You have a brewery already in the database, You have a beer already in the database. Yes, you are going to have to go through and manually hook those up because you didn't do it when you entered them. But you are doing it wrong. You need a table of breweries with a breweryID (PK). That is one entity. Then you need a table of beers with a beerid (PK) and a breweryid (FK) relating each beer to a brewery, If you want a table of wallpapers and other stuff related to each beer put them in a wallpaper table or a whatever table with a wallpaperid (PK) or a whateverid(PK) and give each one a beerid as a FK.

    From now on, when you enter a new beer, you enter it's breweryID (FK) in the record at the same time. Each record in the beers table is one beer and each record has a field relating it to a brewery.

    Show me all the Three Floyd's beers?

    Select *
    from beers
    where breweryid="40"

    You do not need a third table relating breweryid to beerid. No point.
  14. #8
  15. No Profile Picture
    Registered User

    Join Date
    Oct 2012
    Location
    Russia
    Posts
    5
    Rep Power
    0
    I can recommend to come on a site where there are many articles on a theme interesting you.
  16. #9
  17. Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Feb 2006
    Location
    Ringgold, GA
    Posts
    69
    Rep Power
    10
    Oh I know it's conventionally wrong, and I know how it's supposed to work. Just wanted a way to link any record in any table to any record in any other table. It was either make another table like I did, OR have a foreign key for every table in every table.

    I did however link that interface when a pulldown menu is selected. It automatically inserts the reference in the table you call no point.

    It's all good I'll admit it's wrong, but will keep it this way.
  18. #10
  19. 0x800A0C93
    ASP Super Hero (2500 - 2999 posts)

    Join Date
    Jan 2009
    Location
    Ridin' through this world...
    Posts
    2,975
    Rep Power
    1310
    Then why did you even come ask a question?

Similar Threads

  1. Importing from Excel into relational tables
    By geolemon in forum Microsoft Access Help
    Replies: 3
    Last Post: August 28th, 2008, 01:33 AM
  2. Relational help
    By kwakwhan in forum Microsoft Access Help
    Replies: 7
    Last Post: August 2nd, 2008, 08:19 AM
  3. Relational Database Help
    By Benn in forum Microsoft Access Help
    Replies: 2
    Last Post: January 20th, 2008, 09:24 PM
  4. Help with relational database
    By ausjoe in forum Microsoft Access Help
    Replies: 2
    Last Post: August 16th, 2004, 07:34 PM
  5. Relational DropDownList Using VB.NET
    By Rich in forum Code Bank
    Replies: 0
    Last Post: February 5th, 2004, 03:19 PM

IMN logo majestic logo threadwatch logo seochat tools logo