database design (diagram included)

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)

database design (diagram included)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-23 : 03:59:38

Above is a snapshot of part of my diagram. I will go into as much detail as I can here. TBLUSERDETAILS is the main table, and the focus of this question. I am not sure if I should break down further.
TBLUSERDETAILS stores all of the registered members personal information. Question1-5 are varchar(200)'s and Thumb1-5 are tinyints. The rest for the most part are tinyInts's and varchar(15)'s. Question1-5 are all pulled when a members info is looked at and thumb1-5 indicate the 'status' of the members additional photos they have the option to upload. (exists, doesnt exist etc)

I am concerned as to whether I should break these 2 down futher.

I originally put these all in the same table so that 1 recordset could pull all the information needed for the page (this page accounts for 40% of page views) Would it be better to open multiple recordsets? Should I break this table down? Thanks for any insight I've gotta have this design perfect! :) Any other insight would be appreciated as well. Thanks again guys.

Mike

Spyder
SQLTeam Author

75 Posts

Posted - 2002-02-23 : 04:33:01

Your tradeoff is going to be ease of programming versus future scalability and flexibility (i.e., what happens if one day they decide they want 10 questions?). I don't know all of your business rules so I'll give you a couple of examples. Minimally, I think you might want to consider normalizing by creating a parent entity for THUMB and another one for QUESTION. I think you might even want to go one step further and create a cross-reference entity for USER_QUESTION if the relationship between USER and QUESTION is many-to-many. The cross-reference table would contain the primary keys of both of the parent entities plus the attribute(s) for how the user responded to the question. This would allow you to also include descriptive data about the QUESTION in the parent entity. If the USER to QUESTION relationship is just one-to-many then disregard this and just apply the same recommendation for the THUMB relationship to the QUESTION relationship).

Here are some more specifics:

USER --< THUMB -- this represents a one-to-many relationship

THUMB.thumb_id (primary key)
USER.userid (primary key) = THUMB.user_id (foreign key)

USER --< USER_QUESTION >-- QUESTION -- this represents a many-to-many relationship

USER.userid (primary key) = USER_QUESTION.user_id (foreign key)
QUESTION.question_id (primary key) = USER_QUESTION.question_id (foreign key)

QUESTION:
question_id (PK)
question_name
question_descr

USER_QUESTION:
user_id (PK)
question_id (PK)
response

THUMB:
thumb_id (PK)
user_id (FK)
thumb_name
thumb_descr

Edited by - SPYDER on 02/23/2002 04:36:01

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-02-23 : 10:11:12

I agree with Spyder, normalize this table. You state you don't want to because you don't want to open multiple recordsets. You don't have to; normalize the table properly and just just join the child tables (question and thumb) to the parent table (users) when you query. You only need a single recordset. You're setting yourself up for potential headaches with your current scheme.

m2c,
Justin

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2002-02-23 : 16:05:18

SQL is relational, so IMO make as many tables as possible

.

Once you start to be cheap on tables this will always backfire at you on a later stage, ever.

The longer you are a programmer the more you will make things dynamic. If you keep things STATIC key-users will never be able to make small changes themselves.

I make a lot of programs for a lot of customers, I dislike to come back for every wish they have. Besides, it will make you a better programmer if you make things customizeable for customers. In the beginning this will cost you a lot of developers time, but after a while you can recycle your own code and make app's in no time!

So normalize also your field names, this will be of great use later on...

My two cents

Henri

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-25 : 03:00:16

Ok I will tackle this one step at a time... I am working on the thumbnail table first since it is more of a priority

I created it like this

userID(int) --> composite Key (fk)
thumbID(int) --> composite Key
status(tinyint)

UserID stores a number that must correspond to "tbluserdetails".
ThumbID is the ID of the thumb for each user (1-3)
StatusID is the status of the Thumbnail - approved or not.

How does this look to you guys?

Also, from the old design (see above) is it possible to move the data over to the new design??? That would be a life saver!

Nazim
A custom title

1408 Posts

Posted - 2002-02-25 : 03:34:15

Hi Mike,

Follow the recommendations Spyder has given. the design he has come with is perfectly normalized and should help you to greatly.

About adding your old data.

couple of issues will come up.
you have to use a seperate inserts for every Question no and thumb no or can code it using Dynamic Sql.

for a start with insert something on this lines should help you.

insert into tblQuestion(question_name,question_desc) select qname1,question1 from tblUserdetails

insert into tblQuestion(question_desc) select qname2,question2 from tblUserdetails

insert into tblQuestion(question_desc) select qname3,question3 from tblUserdetails

insert into tblQuestion(question_desc) select qname4,question4 from tblUserdetails

HTH

--------------------------------------------------------------

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-25 : 03:50:01

thanks again nazim

About spyders table,

THUMB:
thumb_id (PK)
user_id (FK)
thumb_name
thumb_descr

is mine not essentially the same?

I do not have a name or a description field. Simply a number for each thumb from 1-3, and a "status" column that identifies the status of the thumb. Maybe I am confused somewhere but is mine not normalized??

Cheers

Nazim
A custom title

1408 Posts

Posted - 2002-02-25 : 04:02:13

Coming to what Spyder has recommended

QUESTION:
question_id (PK)
question_name
question_descr

if you dont have a question_name column . you can omit certainly omit it. i dont think it should pose any problems for you if you dont have a field like question_name.

(2) Am not sure about what exactly does your thumb indicates. but from what i could make out Spyder's design looks much better.

Rite now am in hurry will be back after some time . if you want we can explain you the reasons why Spyder has fragmented your table or i think you should go thru normalization concepts once again. pick up a book on Database concepts by C.G.Date(David will be happy with this

--------------------------------------------------------------

Lavos
Posting Yak Master

200 Posts

Posted - 2002-02-25 : 04:25:57

I think you've misunderstood Spyder's design.

Spyder's design has an id column for each thumb. Each thumb then has attributes like UserID (to show who "owns" it) and status.

Your design's primary key is still centered around the UserID column, with an additional field to differentiate between each thumb. It's not "bad" per se, but I think it's going to be harder to work with. I have a sneaking suspicion you're going to end up writing statements like this:

SELECT Status FROM Thumbs WHERE UserID = @USerID AND ThumbID = 1
SELECT Status FROM Thumbs WHERE UserID = @USerID AND ThumbID = 2
SELECT Status FROM Thumbs WHERE UserID = @USerID AND ThumbID = 3

It's also going to be harder to maintain. Write a statement to insert a new thumb into the table. With your design you've got to know under which ThumbID to insert. What happens when you want to allow 5 thumbs? Or unlimited thumbs? That's a lot of code to go back over. With Spyder's design you just put a constraint on the table to enforce your rule of 3 thumbs and write everything to work with however many thumbs the user has in the table. Want to expand the number of thumbs (or reduce) then just change the contraint. Easy maintenance :)

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

samrat
Yak Posting Veteran

94 Posts

Posted - 2002-02-25 : 04:44:30

The design looks pretty good. And, ya certainly u can move the data between the two structure by writing simple DTS package. If u are not sure about writing the DTS package then let me know..

Cheers,

Samrat

Nazim
A custom title

1408 Posts

Posted - 2002-02-25 : 07:26:40

Mike on second thought this looks fine to me.

userID(int) --> composite Key (fk)
thumbID(int) --> composite Key
status(tinyint)

--------------------------------------------------------------

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-02-25 : 17:32:22

Now i've gotta take this question 1 step further here regarding a JOIN vs another recordset. Here's the situation

I have selected 1 row from the main table "tblUserDetails". Now I would like to get all the thumbs from "tblThumbs". I can do a join as such

select * from tbluserdetails JOIN tblthumbs on tbluserdetails.userid = tblthumbs.userid where tbluserdetails.userid='500'

or I can open a New Recordset

select * from tblthumbs where userid='500'

If I do a join I am bringing back the main table("tbluserdetails) row back 3 times am I not? I'm thinking that a new recordset might be more efficient?

Performance is a key issue because the site is currently lagging, and we can't afford new hardware. And this 1 page is about 40% of page views. Thanks again I'd be screwed without you guys!!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-02-25 : 17:43:58

Mike,

That is the one of the benefits of normalisation...

Why use the JOIN when you have the already have the "Key" piece of information already (UserID)..

If you just need the data from tblthumbs then this all you need


select * from tblthumbs where userid='500'

Check your indexing stratetgy....

DavidM

Tomorrow is the same day as Today was the day before.

samrat
Yak Posting Veteran

94 Posts

Posted - 2002-02-26 : 07:04:03

I totally agree with you Byrmol, mike as byrmol suggested make sure you have a good indexing startegy on the table. And ya, just check the fill factor of your pages as optimising this things helps a lot in enhancing the performance.

Cheers,

Samrat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources