db design question

Last Post 23 Sep 2010 09:44 AM by Rodgersnotes. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dbandee
New Member
New Member

--
04 Aug 2010 08:46 AM
i have db designed like this

cust
(custid, name, addr1, addr2, city, state, zip)
vendor
(vndrid, name, addr1, addr2, city, state, zip)
store
(storeid, name, addr1, addr2, city, state, zip)

I was asked why not to have table with addresses only and FK from major entities to it?
I did not have good answer

What are your thoughts?

Also as continuation to previous question

Each entity is allowed to have multiple comments.
I thought of something like comments table for each entity

Cust_Comments, store_comments, vendor_comments etc.

or shoud i have one table comments?

Your opinion is appreciated

Thanks

russellb
New Member
New Member

--
09 Aug 2010 10:46 AM
I like to have comments tables seperate. 1 for customers, 1 for vendors etc, but either is fine. Same with addresses, though i've seen others choose to make one address table. Can't Foreign Key a combined address table though. For example, the address should be Foreign Keyed to Customers if it's a customer record, Vendors if it's a vendor record, Employees if it's employee record. It's a hassle to maintain and enforce data integrity. Also, permissions are easier to manage if you don't combine it. Say for example, I'm not allowed to see Employee addresses or Customer addresses, but I can see Vendor addresses. How to enforce that efficiently if it's all in one table? (views is the answer, but still, an unnecessary hassle).

I'll put addresses with the customer/vendor/employee record. Unless it is possible that they have multiple addresses, in which case I have a CustomerAddress table...
gunneyk
New Member
New Member

--
09 Aug 2010 11:49 AM
I second Russells thoughts.
Rodgersnotes
New Member
New Member

--
23 Sep 2010 09:44 AM
Addresses.  These are always a little tricky.
In theory, they might be repeating.  But just how many addresses overlap?  
If you made a lookup table, would a store, vendor, and customer share the same address?
Probably not.

However, it can make sense to have a lookup table for postal/zip codes.

Comments.  Are you thinking of multiple comments over time?
Or just one for the customer record?
If just one comment on the customer record, I would use the NVarchar datatype in the customer table.
NVARCHAR column can store up to 4000 characters (2 bytes per unicode character)
The data will be compressed.  No big increase in space.

But if you needed a field for an object for say a picture, that I would create into a separate table.
And only insert a record if there was a picture to insert.

See some writing I did on a similar subject at:

http://rodgersnotes.wordpress.com/2...gle-table/

I've compiled many other DB Design Mistakes to Avoid at:
http://rodgersnotes.wordpress.com/c...-to-avoid/


Hope they help.  



Acceptable Use Policy
---