Best Table Design - 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)

Best Table Design

Author

Topic

Dave123
Starting Member

3 Posts

Posted - 2003-07-05 : 12:26:59

I am wondering the best table design to handle this one-to-many relationship.

I have a table that contains real estate listings, and each real estate listing can have many images associated with it. However, I need at least 2 images for each listing, one declared the "small image" and one declared the "large image".

My initial thought is to build the tables like this-

Listing
-------
ListingID (PK)
SmallImageID (FK)
LargeImageID (FK)

ListingImages
-------------
ListingID (PK)
ImageID (PK)
SortOrder (numeric (4.0) not null)

Image
-----
ImageID (PK)
ImageFileName (not null)

However, it seems rather redundant to have the SmallImageID and LargeImageID FK's as well as a table of ListingImages. Is it better to add small image and large image boolean atrributes down to the ListingImage table like this?

Listing
-------
ListingID (PK)

ListingImages
-------------
ListingID (PK)
ImageID (PK)
SortOrder (numeric (4.0) not null)
SmallImage (bit - not null)
LargeImage (bit - not null)

Image
-----
ImageID (PK)
ImageFileName (not null)

I appreciate any help on understanding the best design. Regards,

Dave

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-05 : 12:59:49

SmallImage (bit - not null)
LargeImage (bit - not null)

This is presumably one or the other but not both?
Then type bit - or type tinyint for flexibility.

Is it possible that the image can be both small and large - i.e. only one image for the entry (small used in both cases). Do you have default images in case there is no image?

What is the SortOrder?
I'm guessing tat this is so that you can hold a collection of small and large images for each entry?

Really depends on what you want to display and how you intend to maintain the images but:
If you want to disply all images then your first op[tion is incorrect as it allocates a single pair to the listing.
If you want to display this single pair in some cases (SortOreder 0?) and the collection in others then I would go with option 1 as it allows more flexibility in the allocatio of the special pair (but give the fields a better name).

Just thought - maybe the SortOrder is so that you can display images across listings?

Another thought.
Are Images entities or are the filenames attributes of the listing?
Is an image shared between Listings? (not having a ListingID on Image would imply this).Are any images not allocated to a Listing
i.e. do you need an Image table with an ImageID or could you just use ImageFileName?

Listing
--------
ListingID
SmallImageFileName
LargeImageFileName

Again depends how you maintain them.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-05 : 13:06:52

Think it would be better:

Listings
-------
ListingID (PK)

ListingImages
-------------
ListingID (PK)
ImageID (PK)
Order (int not null)

Images
-----
ImageID (PK)
SmallOrLargeImage (bit - not null)
ImageFileName (not null)


--------------------------------
ListingID    ImageID      Order
--------------------------------
    1           77          1
    1           44          2
    1           11          3
    1           22          4
    2           88          1
    2           55          2
--------------------------------

In short, it depends on what are these Small and Large images
on front end... Seems they should be treated as different
entities, like e.g. Color and Height of a object. In this case
they should be stored in different tables and we shouldn't be
confused by their false likeliness.

- Vit

Edited by - Stoad on 07/05/2003 16:50:00

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-07 : 10:04:18

Did I mention I hate identity...

Hey a listing is probaly a house, right?

Why not use it address as the key?

The have a child table that has all of the pictures for that address.

You're not going to be moving the houes anytime soon (unless they tear it down).

Just put an image capture date on it for relevance.

PS And for the "best table design" I think Candles are nice.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources