Relational tables

[KillGorack]

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.

[gk53]

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

[KillGorack]

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..

[bigmike1212]

I don't really follow your problem.

You have a table of breweries.

breweryid	breweryname
1		Ranier
2		Goetz
3		Heim
4		Redhook

You have a table of beers.

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?

[gk53]

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.

[KillGorack]

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

[bigmike1212]

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.

[sycleDelt]

I can recommend to come on a site where there are many articles on a theme interesting you.

[KillGorack]

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.

[bigmike1212]

Then why did you even come ask a question?