how do I relate these tables? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how do I relate these tables?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-09 : 15:16:20

I am creating a new application and am having an issue with one thing thats preventing me from moving forward. I'll try to explain best I can here.

The application I am creating is a website that is going to showcase different types of media. (videos, pictures, maybe mp3's etc). I am not sure the extent of what is going to be available yet, but I need to be able to add to it when necessary.

All these different "types" of media need to be able to be voted on from users as well as commented on.

For this reason I think I need a table such as "Items" with an identity column that would be commented on and voted on. Is this correct?

If this is correct, how would I relate it to each other media type? They different types might have different properties etc, so would I have a table for videos, one for audio etc ?

Any example/ guidance on this is greatly appreciated.

Thanks once again
Mike123

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 15:28:25

They must have some common attributes....

And I wouldn't use IDENTITY...~~I would use the filename and filepath of the item as the key.~~ I take that back...I would prefer some natural key for this though. Maybe just the filename.

And I would have the item type an actual word, like video.

I wouldn't use an IDENTITY of 1 for video.

I would us

ITEM
ITEM_TYPE
DETAILS_VIDEO
DETAILS_PICTURE
DETAILS_ect
ITEM_VOTES
ITEM_COMMENTS

MOO

Best thing you should do is collect ALL of the attributes you know about the project...just makwe a list of all of them

Don't worry about what they are or how they relate...just do a core dump.

Whne you collected them all, start and group them together. Again, don't worry about what to call the groups yet. Make sure the attributes have some common ground.

You will then begin to see entities form. Ask questions about the data...1 or many of thing...this is how you build a logical model...

Sound good?

Brett

8-)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-09 : 17:55:11

Hi Brett,

Just to clear things up a little. The comments and votes that would not just a column in the item table. They would have to be a seperate table as there could be dozens of each per video.

I don't like the idea of using the filename because I see a possible need for renaming in the future. (Search engine reasons)

I'm not sure I understand what u mean here

ITEM
ITEM_TYPE
DETAILS_VIDEO
DETAILS_PICTURE
DETAILS_ect
ITEM_VOTES
ITEM_COMMENTS

Are you suggesting have info about the video and picture details in the same table?

What about having multiple tables for each media types comments? This doesnt seem like a good idea at all but would be easy to do.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-10 : 10:02:06

quote:
Originally posted by mike123

ITEM
ITEM_TYPE
DETAILS_VIDEO
DETAILS_PICTURE
DETAILS_ect
ITEM_VOTES
ITEM_COMMENTS

These are tables.....


Parent			Child

ITEM_TYPE		ITEM
ITEM			DETAIL_VIDEO
			DETAILS_PICTURE
			DETAILS_ect
			ITEM_VOTES
			ITEM_COMMENTS

So make ItemID and IDENTITY then...and don't try and store the files in the datbase...store them on a server...preferable on a fixed drive separate from the database and o/s

Brett

8-)

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-10 : 18:29:27

quote:
Originally posted by X002548

quote:
Originally posted by mike123

ITEM
ITEM_TYPE
DETAILS_VIDEO
DETAILS_PICTURE
DETAILS_ect
ITEM_VOTES
ITEM_COMMENTS

These are tables.....

Parent Child

ITEM_TYPE ITEM
ITEM DETAIL_VIDEO
DETAILS_PICTURE
DETAILS_ect
ITEM_VOTES
ITEM_COMMENTS

So make ItemID and IDENTITY then...and don't try and store the files in the datbase...store them on a server...preferable on a fixed drive separate from the database and o/s

Brett

8-)

Hi Brett,

Thanks again for the followup. I have a few concerns with this design:

I was planning on having the ITEM_VOTES and ITEM_COMMENTS as their own table, which is easily changed.

The thing with the child table

Parent Child

ITEM_TYPE ITEM
ITEM DETAIL_VIDEO
DETAILS_PICTURE
DETAILS_ect
ITEM_VOTES
ITEM_COMMENTS

Is that I am going to have details on the video OR the picture. The columns for these different media types may be different.

I have the files on a fixed drive separate from the database and o/s as you have suggested.

Thanks again,
Mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources