is my DB design is good:?

[leeshadmi]

Hi i am a beginer in DB design.
I using SQL Server 2005 express edition and i will use the database with VB.Net 2008.
I need to create a Receipt application that will save all the data from the receipt in the DB.
Here is the tables strcture that i created, can you please tell me if i am in the right durection:-)?
TB_Vendor: VendorID
TB_VendorDetails: VendorID, VendorName,VendorAddress,Mail,Web
TB_VendorPhons: VendorID,PhoneNum
TB_Receipt:VendorID,RecepitID
TB_RecepitDetails:RecepitID, Total,Date

I really not sure about this structure but this is why i came here to ask for you advise.
Where ever i wrote ...ID i mean this field will be int, prime key, auto number.
Any help will be wellcome.
Thanks
:wave:

[Teranoz]

I would put all unique data from vendors into one table with the vendorid as primary key autonumber
In case you have multiple details from vendors like contact persons I would make a seperate table for them linking to the vendorid in the vendors table

imho its not very logical to make a table for the vendorid alone because you create unneeded joins for data you would need anyway in most cases

[davide++]

Hi all.

I agree with Teranoz; tables with one-to-one relationships make no sense in this case. So, you can define two tables, TB_Vendors and TB_Receipts, with one-to-many relationship.

PS: I suggest to name tables in plural.

[leeshadmi]

Can you write it so i will understand it better like i wrote in the first post?
Thanks

Sorry for my lake of experties in DB design.

[davide++]

Hi all.

You have two objects (better, two entities), the Vendor and the Receipt; the Vendor can have many Receipts, so there's a one-to-many relationship between vendors and receipts (one vendor, many receipts).
So, there're two tables


TB_Vendors
VendorID (primary key)
VendorName
VendorAddress
PhoneNum
Mail
Web

TB_Receipts
VendorID (primary key, link to VendorID of TB_Vendors)
RecepitID (primary key)
Total
Date


According this model, a vendor can have one, and only one, phone number. To manage multiple phone numbers for one vendor You can easily extend the model adding a TB_Phones table with one-to-many relationship between TB_Vendors (again, one vendors, many phone numbers).

I hope this will help you.

[leeshadmi]

Thanks so much i got it and i will do this.
:thumb:

[leeshadmi]

Can you please take a look on this design and tell me if it's ok.
I attached the sdf file just in case and picture of it in jpg format.
Thanks very much for you help

[davide++]

Hi leeshadmi

I hope I'll give you an useful help although I don't know the problem domain (and in spite of my bad English).
The model seems correct for me, but I have some remarks.
First, I don't understand the relationship between Receipts and Branchs; there're many vendors for one branch, OK, and one vendor does many receipts (and OK again, there's a one-to-many relationship between Vendors, 1, and Receipts, many). You don't need to link Receipts to Branchs, because you can find what branch one receipt belong to; starting from Receipts you find one vendor, and then starting from this vendor you find one branch. So the relationship between Receipts and Brachs is unnecessary.
Then the relationship between Branchs and VendorPhons is strange. It's an one to one relationship because the primary key of VendorPhones is the same primary key of Branchs; so according this model one branch can have only one phone. You should extend the primary key of VendorPhones adding the PhoneNumber field.

[Leon.Kennedy]

Just a personal opinion, but I prefer to have the tables tbl_Vendors and tbl_Receipts, and then have an intermediary table called tbl_Vendor_Receipts with 2 fields:

VendorID (Number, Long Int, Primary Key)
ReceiptID (Number, Long Int, Primary Key)

You would then have a 1 to Many relationship back to the Vendor Table, and a 1 to 1 relationship back to the Receipt Table.

Why do it this way? You don't want Receipt or Vendor information getting corrupted or skewed. And by keeping the Vendor ID out of the Receipt table, you won't have to worry about updating a record in that table in the event the vendor on the receipt is incorrect - you would merely need only update the record in the tbl_Vendor_Receipts table.

And although this will probably never happen (and by my guess SHOULDN'T happen), this gives you the flexibility to have multiple vendors on 1 receipt. I know - shouldn't happen. But then again, anything is possible. Just when you think you have everything covered, someone comes along and asks to have a few separate entities that are part of a larger corporation get billed for transactions, but you have to tie everything back to the one receipt so the larger corporation gets the credit. It can (and will) happen.

This is just one humble opinion, though.

[leeshadmi]

Thanks but isn't this right?:
VendorID can be WallMart and Branch can be mulipy like NY branch and
LA branch.
Am i wrong?
Can be many receipt from one vendor and from many branch sites
if i am wrong please correct me.

Leon.Kennedy: you mixed up all my concept how will the DB design need to be by the description you wrote.

Dont forget i am a very begginer with DB's
Thanks
:thumb:

[davide++]

Hi all.

As I said, I don't know exactly the problem domain. All depends on what relationships there are between vendors, receipts and branches.
OK, we have Mr. WallMart as vendor, and two branches, one in New York and the other in Los Angeles; receipts are made by one vendor, right? Now, can WallMart work for both branches?
If yes, there's a many-to-many relationship between Vendors and Branches, and the best solution is adding an intermediary table that implements the many-to-many relationship as Leon.Kennedy suggested, and the link from receipt to branches is necessary in order to know where a receipt was made, in NY or in LA. If no, ie one vendor can work for only one branch (and there are many vendors for one branch) there are one-to-many relationship between vendors and branchs and in this case you don't need the link from receipt to branch because there's a relationship between receipts and vendor yet, so when you know who made a receipt you'll know where it was made; of course, you can keep the relationship between receipts and branches, but it's a redundant information.

For Leon.Kennedy. I didn't understand what you wrote; when you define an intermediary table you establish a many-to-many relationship between two tables, do you?

[Leon.Kennedy]

For Leon.Kennedy. I didn't understand what you wrote; when you define an intermediary table you establish a many-to-many relationship between two tables, do you?
No, you are establishing a 1 to many relationship. Example:

You have VendorID 1234 in tbl_Vendors. You are only going to have this VendorID 1 time in tbl_Vendors. It can show up multiple times in the tbl_Vendor_Receipts table. Therefore, a 1 to many relationship from tbl_Vendors to tbl_Vendor_Receipts on VendorID.

You can also, conversely, have ReceiptID 1234 in tbl_Receipts. Going by the concept of having only 1 vendor per receipt, this receipt would show up 1 time in tbl_Receipts, therefore having a 1 to 1 relationship from tbl_Receipts to tbl_Vendor_Receipts. However, if the case exists where you can having multiple vendors on 1 receipt, then ReceiptID could show up many times in tbl_Vendor_Receipts. This would give you a 1 to many relationship from tbl_Receipts to tbl_Vendor_Receipts.

And you can use the same concept with tbl_Branches and tbl_Vendors. Vendors may be associated with multiple branches, and each branch can have multiple vendors. You establish a 1 to many relationship from tbl_Vendors to tbl_Branch_Vendors, and then a 1 to (potentially) many relationship from tbl_Branch to tbl_Branch_Vendors.

Make sense?

[davide++]

Well, I see...

Using the intermediary table you implements a many-to-many relationship, but you manage it as one-to-many relationship on Vendors side, and one-to-one relationship on Receipts side, so you obtain an one-to-many relationship between Vendors and Receipts, at the end. But what are the advantages of this approach, a part from getting ready to manage a real many-to-many relationship?

[Leon.Kennedy]

Well, I see...

Using the intermediary table you implements a many-to-many relationship, but you manage it as one-to-many relationship on Vendors side, and one-to-one relationship on Receipts side, so you obtain an one-to-many relationship between Vendors and Receipts, at the end. But what are the advantages of this approach, a part from getting ready to manage a real many-to-many relationship?
The major reason for this is data integrity. If the vendor on a receipt changes, I won't be updating the receipt table. If a receipt has multiple vendors on it, you won't have to worry about what the primary key for the receipt table is. And the same thing on the reverse side for the vendors table. Not to mention that it brings the database into 3NF.