schema help. - 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)

schema help.

Author

Topic

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-10 : 11:37:38

Hello again,

I'm now in the process of designing a new database, which will be used as a back-end for one of our online stores.
I hae done this a few times, but now I've realized that I've done it wrong those few times.

Previously, I would a tables like:
Users: UserName, FirstName, LastName, MiddleName, Email, PhoneNumber, Street, City, State, Zip

However, I need something more scalable; something where the user is not tied in to only one address, one phone number, one email address, and such.

This is where I get confused.

I've come up with this so far:
Users: UserName, FirstName, MiddleName, LastName
UserInfoTypes: UserInfoTypeID, UserInfoTypeName (home, work, mobile, laptop, etc)
UserInfo: UserInfoID, UserInfoTypeID, UserName, Street1, Street2, City, State, Zip, Email, Phone

The problem that I see with this is, for example: If the UserInfoTypeName is "Mobile", than they will obviously not have a Street1, Street2, City, State or ZIP in the UserInfo table, which creates a lot of unnecessary empty space.
What do you all recommend?

Secondly, for the items part:
Previously, I had tables like:
Items: ItemNumber, ItemName, ItemDescription
ItemPrices: ItemPriceID, ItemNumber, Waist, Length, Size, Price

So, let's imagine that item number 123 is a hat, so with a query like "SELECT * FROM ItemPrices WHERE ItemNumber = '123'", I'd get something like:
1, 123, NULL, NULL, NULL, 12.50
Or, if 456 was a shirt:
2, 456, NULL, NULL, XL, 12.50
Or, if 789 was a pair of pants:
3, 789, 32, 32, NULL, 12.50

See what I'm getting all? All of the NULL values. I could give Waist, Length and Size a default value of "N/A", but still, it's a lot of unneeded data.
What do you recommend? Something with Categories?
Items: ItemNumber, ItemName, ItemDescription, CategoryID
ItemCategories: ItemCategoryID, ItemCategoryName
Then what?
This site is going to have items with multiple sizes and colors, and the price will depend on the items size.

Help help? :-p
Let me know if I'm not being clear enough!

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 13:48:53

Hi fiXXXerMet,

Wow - this is a big topic!

No-one else seems to have replied to this yet, so I'll have a stab.

First, here's some background reading material for you:

http://www.datamodel.org/NormalizationRules.html
http://www.sqlservercentral.com/columnists/dpeterson/lookuptablemadness.asp

This is real-life data that many different people have represented before, so one starting point could be to try to find those and see how they've done it. No doubt there will be examples of many different way of representing this real-life data in a system, and all will have reasons for doing it the way they have. All of that could help you with your design.

Your first design thought, in general, should be a fully normalised structure, and then you can go from there. If that design becomes too unwieldy, you can then think about how best to de-normalise (e.g. using your 'type' tables) so it best meets your all your system requirements (which only you will know).

So (from the top of my head) start with listing out your different 'things' and their attributes:

User: Id, UserName, FirstName, MiddleName, LastName
UserAddress: UserId, Type, Street1, Street2, City, State, Zip
UserEmail: UserId, Type, Description, Email
UserPhone: UserId, Type, Description, Phone

Hat: Id, Description, ItemId, Price (although don't hats have sizes?

)
Shirt: Id, Description, ItemId, Size, Price
Pants: Id, Description, ItemId, Waist, Length, Price
etc...

You may ultimately end up with your 'ItemCategories', but the stuff above should hopefully get you thinking about why you've ended up going that way.

Well hopefully that's something to get you started...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-10 : 14:26:54

It is quite a big topic, so thanks for taking the time to help me.
I'm going to give your layout a shot. I figure after I design it, I can see what needs improving, and I see where you're going with that kind of design.
One question:

Err,
Will it hurt to use UserName as the PK instead of an ID column? UserNames will contain letters and numbers.

*EDIT*

What about PKs in the User tables?

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-10 : 14:36:32

I'm thinking again:
With your model, what about when I want to list all of the different categories.
Since these are no longer in a table - they are the tables - how would something like that work?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 14:44:29

quote:
Err,
Will it hurt to use UserName as the PK instead of an ID column? UserNames will contain letters and numbers.

You certainly like your big topics!

The short answer is 'probably not' - or even 'no'. The long answer could take a long while indeed!

Read up on surrogate keys and natural keys: http://www.google.co.uk/search?hl=en&q=sql+server+surrogate+natural+key&btnG=Search&meta=

quote:
What about PKs in the User tables?

I'd say UserId if decide to have that, and UserName if you don't! You'll understand more once you've read around the stuff above.

Does anyone else have some opinions they want to chirp in with?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-10 : 14:52:52

I'm seeing another problem, that I'll need another table for each 'thing' I want to add, such as "fax" "phone" "address" "email" "etc" "hat", "shirts", "pants"

See what I mean?:

Though I suppose Fax and Phone could go under the same table.
This is why I was thinking of a table that held categories for products, and something similar for contacts.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 14:58:19

quote:
Originally posted by fiXXXerMet

I'm thinking again:
With your model, what about when I want to list all of the different categories.
Since these are no longer in a table - they are the tables - how would something like that work?

I wouldn't say it's 'my' model. It's more like the model which arises out of standard normalisation techniques. I'm just helping you figure it out (or at least trying to!).

Anyway, with your question, you're getting how you want your system to behave - and that could well influence your data structure.

My first question though would be: Why do you want to list out all the cateories? Could you describe a scenario?

This asked, I may not have much more time to help you, and I'm hoping someone else might chip in...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 15:03:57

quote:
I'll need another table for each 'thing' I want to add

Yes - of course. That's kind of the point!

quote:
Though I suppose Fax and Phone could go under the same table.
This is why I was thinking of a table that held categories for products, and something similar for contacts.

Did you read the articles I posted? The 'Lookup table madness' article might make you think again...

. Read it!

As I say you could end up where you started, but it's best to reason it all through, understand the pros/cons and know precisely why you've gone the way you have. Particularly this first time!

Okay - I'm going home now

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-10 : 16:03:33

quote:
Originally posted by RyanRandall
My first question though would be: Why do you want to list out all the cateories? Could you describe a scenario?

Such as if a user wants to browse items by category:
Category List -> Items in selected Category -> Individual Item Description


Shirts
  |---->Metallica
          |------->Size: L  Price: $10
  |---->Flogging Molly
  |---->White Zombie
Pants
Hats
Etc

quote:

quote:
I'll need another table for each 'thing' I want to add

Yes - of course. That's kind of the point!

Agreed, however there can be very many 'things', so this doesn't seem like it would save us much time... But I guess that isn't the main reason behind a database.

quote:
Did you read the articles I posted? The 'Lookup table madness' article might make you think again... . Read it!

I just read the second one, and I do see where you are getting at!

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-10 : 16:46:51

How's this?

From what I see,
If I have "Home", "Work", and "Mobile" in "UserInfoTypes", then a user can have an Address, Phone, Email and Fax for each.

For the Items:
Each Item has a Manufacturer and a Brand.
Short Sleeve, Long Sleeve and No Sleeve all have different options, so they're in seperate tables.
For the pants:
Each pair of pants can have any number of Waist and Length sizes that do not relate to each other, and in our system, Price relates to the Waist size.
Finally, each Item can have any number of options, such as:
"Name Embrodiery", "Logo", "Etc".

fiXXXerMet
Starting Member

18 Posts

Posted - 2006-05-11 : 13:43:20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources