How to design the table

Question

3.33/5 (3 votes)

See more:

I have tables like below
t_company has companyid, companyname;
t_department has departmentid, departmentname, companyid;
t_goodstype has goodstypeid, goodstypename, departmentid;
t_goods has goodsid, goodsname, goodstypeid;

In my project, in many times I would like to see goods and goodstype a company has.
Is the table up good designed.
Or should add a row like companyid to table t_goodstype and t_goods;

Posted 5-Jan-13 16:30pm

qinritukou

Add a Solution

Comments

Jörgen Andersson 7-Jan-13 16:30pm

Can a certain goodstype only be handled at one department?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tharaka MTR · Answer 1 · 2013-01-05T17:32:00

Solution 1

Your table design is good. Instead of creating a new table, you can create a VIEW by joining the table to giving the output as you like. consider what are the parameter you want to filtering out and what are the data fields you want to get as output.

Also you can create a User Defined Table Function for this purpose.
Performance wise, "it depends" on the data and the filtering criteria. I've seen performance improvements from moving from views to UDFs, and also from moving from UDFs to views.

Posted 5-Jan-13 17:32pm

Tharaka MTR

Comments

qinritukou 6-Jan-13 0:51am

Thanks for your answer, I wonder to know if you know any good books about the database design and the performance of different designs. Thanks ahead.

Tharaka MTR 6-Jan-13 7:55am

following links are very helpful for me also
http://msdn.microsoft.com/en-us/library/ff647793.aspx
http://msdn.microsoft.com/en-us/library/ms172984.aspx

Books
for biginers
http://www.amazon.com/Learning-SQL-Alan-Beaulieu/dp/0596520832
http://www.amazon.com/dp/0201752840
Advanced
http://www.amazon.com/dp/0596008945
http://www.amazon.com/dp/0201791692

if you are OK with my answer, please "Mark as Answer"