PRoblem designing db table

[LordOsh]

Hi,
I need your advice. I have a trouble designing my db structure. Imagine I have a links section on my website. These links could be either to files, or to other pages on my website or links outside. Now I need to, somehow, store these links in db. But I dunno how. I thought I could store it as a string containing HTML anchors, but I can't do that because I won't be designing the website, so I dunno how they are going to show the results (eg. what php script will show particular page - another table in db). Which means I can't 'hardcode' the achors. I'm just the guy who is going to create&fill the database.
I know my explanation ain't that good, hope u will understand.

So basically we have:
table 'documents' : id, html text , LINKS (files - this document is file's parent, other documents - I know the id, links outside of the site) ...
table 'files' : name, parent (document id), raw data ...

How to store LINKS in table 'documents'?


Thanks in advance,
Ondrej

[chrismartz]

Maybe I just don't understand, but why don't you just insert the "http://" of the site into the db column?

[LordOsh]

I can do that for 'links outside'. Because I know this link goes to, say, google.com. But for other links (files, documents...) I don't know their http as I'll not be designing the website. I just know theirs IDs in the db.

[CarolineBogart]

If you can rely on the coders to use the db correctly then maybe store the links as individual units of information that they then construct --

table: links
columns:
href
title
target
anchor

Might have data like:
href: www.manchesteranimalshelter.org
title: Manchester NH Animal Shelter
target: _blank
anchor: Manchester NH Animal Shelter

The programmer should construct:

<a href="$href" title="$title" target="$target">$anchor</a>

[LordOsh]

I'm really sorry for explaining the whole think clear. The problem with the solution you suggest is that I know the "href" only when the link points to a different site. When the link points to a document or a file on my site I know only it's database representation (e.g. it's IDs). So, supposing that I create the links table like you said, I'd have to have column url, doc_id, file_id, whatever_else_id and only one of these columns would be used for every row in this table depending on the link type. I don't thing this would be a good design.

[CarolineBogart]

how do you address an internal document by its id when you do know all of the info for that document, what is the syntax for that?

[LordOsh]

As I said before, I'm only creating the database (and filling it with quite large data set that already exists). Just for testing purposes I have my own php scripts to show the data. But the company that I'm working for is going to use their own system (some CMS actually).

[russell]

caroline is right. sounds like u can design it how u see fit, and let the application developers adapt to your design...

[CarolineBogart]

thanks russell - lordosh it does sound like you're trying to solve a problem when you haven't got all the pieces. You can only store what you know about. Store those items as discretely as possible (in their lowest common denominator form, not "secretly" :-)) and you've got the best you can do without knowing more.

could you please be more specific about the tables so that we can determine if we're missing something?