SQL Tree Information - 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

General SQL Server Forums

New to SQL Server Programming

SQL Tree Information

Author

Topic

bthart
Starting Member

2 Posts

Posted - 2012-06-09 : 08:43:56

Hi Everyone,

I am creating a database to manage customer paint colour selections. Customers usually like to touch up but forget the colour they chose. To assist this I have the following information tree.

Customer > Project > Room > Item/Colour

Each level has a database with Primary ID'S linking them together. This would mean that a customer can have many projects, which each have many rooms in them and so forth.

The problem comes however when a second customer wishes to work on the same project. It's not possible to link primary ID's anymore as there is now more than 1 ID to link to.

To make it clearer - I need to somehow link 2 primary ID's to the one record.

Hopefully you understand my dillema and have a possible soloution.

Thanks,

Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-09 : 13:31:27

nope...your scenario is not quite clear from your explanation.
Post some data in below format to illustrate the exact scenario and then explain what you need as output

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-10 : 17:18:45

Hi Brian.

If I understand correctly, you have a table called project, with at field pointing to the id of the table customer.

As I see it, you have two choices, you be able to have one and the same project point to several customers:
1) add as many fields in the project table, as you will ever need costomers to point to the same project
2) remove the customer id field from the project table, and create a customer/project link table - you can then have as many customers pointing to the same project

If the choice were mine, I would choose solution 2, but then again, I don't know the size/complexity of your project and how many places you need to correct in your source code.

bthart
Starting Member

2 Posts

Posted - 2012-06-10 : 22:36:06

Thanks for that....

I was thinking of the first soloution that 'bitsmed' provided, however it does limit my application quite a bit. I hadn't thought of the second soloution - I think it should work good though.

Thanks,

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources