Home » SQL & PL/SQL » SQL & PL/SQL » Guidance for better table design (Oracle, 9.2.0.1.0, Windows XP)
Guidance for better table design [message #429963] Fri, 06 November 2009 09:35 Go to next message
Moksha
Messages: 16
Registered: October 2009
Junior Member
Hi!
I have requirement to store all the products related to a company. There would be multiple companies and each company can have multiple products and each product can belong to multiple companies. So, there is many-to-many relationship.

I have one table called company which contains code as primary key and other field is name. Now, I have another table companyproducts which has code with foreign key refernce to code in the company table.

But, with this table structure, I see that in the companyproducts table the company code gets repeated though with different product names. Is this a good design? If not can you guide me with good solution for this?

If this question seems to be silly, please do not mind, but I need some guidance as whether the table structure is going in the right direction or not.

Thanks,

Regards,
Moksha.
Re: Guidance for better table design [message #429965 is a reply to message #429963] Fri, 06 November 2009 09:37 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Minimum of 3 tables:
Product
Company
product_company

where product_company is a junction table sitting between the other two.
Re: Guidance for better table design [message #430011 is a reply to message #429963] Fri, 06 November 2009 15:46 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Do you know what the following are:

PRIMARY KEY
UNIQUE KEY
FOREIGN KEY

If not you need to go to college, find a mentor, buy a book, or google the internet, in order to school yourself in basic database design and normalization theory.

If you know what they are, then please list out your tables for us with these things labeled and then explain to us how each of the requirements you described is met by your table design. For example you said you had a company table:

COMPANY

CODE
NAME

Does it have PK, a UK, a FK? What is each. Indeed once you do this you may figure out what you need without us.

Good luck, Kevin
Previous Topic: Oracle Standard vs Oracle Enterprise User
Next Topic: forming insert value dynamically based on columns in destination
Goto Forum:
  


Current Time: Mon Dec 05 02:54:50 CST 2016

Total time taken to generate the page: 0.07791 seconds