November 8, 2010 at 7:41 am
Our company has a portal site managing a product line A. Now a new product line B needs to be added which is a totally different product, but some existing tables are reusable, Account, Address, PhoneNumber, Device, Salesrep, Users, etc.
A) Should I create a new database for product line B? A salesrep may work on both product line A and product line B, so as a user who may mantain 2 product lines. How to identify the same salesrep for the comissions of 2 products?
B) Or put product line B in the same database which would add 30 extra tables, that will be easier to work with those reusable tables.
What about if product line C and product line D are needed?
Your help is greatly appreaciated!
November 8, 2010 at 8:06 am
A few questions . . .
The last point is especially important -- the easier you make it for us to visualize, the better we can help you.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 8, 2010 at 9:15 am
As Ray points it out there are a lot of questions to be asked.
Most important questions I would ask to myself if I was in your position is:
- Is this the same application?
- Would B require the same backup/recovery strategy A requires today?
- Does A and B have to be consistent which each other?
A set of "absolutely" answers would point my nose in the path of adding product B to the same database.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2010 at 11:26 am
Thanks for the replies.
Here is the basic table relationships.
Account
AccountGUID (PK)
SalesrepGUID (FK)
AddressGUID (FK)
PhoneGUID (FK)
ProductA
ProductGUID (PK)
AccountGUID(FK)
Address
AddressGUID(PK)
Phone
PhoneGUID(PK)
Salesrep
SalesrepGUID (PK)
User
UserGUID (PK)
UserAccount (1 user to many accounts relation)
UserAccountGUID (PK)
UserGUID (FK)
AccountGUID (FK)
Since all existing accounts are of Product A. If I put Product B in the same database,
1. I need a lookup table
ProductLine
ProductGUID (PK)
ProductLine (Product A/B/C)
2. Add a new column ProductGUID to [Account]
3. Update [Account] /ProductGUID with Product A for the existing ones.
4. If a account has both product A and B, do I need to create 2 accounts that 1 for A and 1 for B?
The result I want see is that if I am a user associated with 2 accounts (2 products) then when I login to portal i am able to see 2 product lines.
November 8, 2010 at 2:33 pm
Sorry -- got hung up this afternoon, and didn't get a chance to respond.
Right now, I'd say put it in the same database, but I say this having not taken the time to look through your last post (like I said, I had a busy afternoon). If I'm able to do so (no guarantee), I'll take some time to digest it and post some more thoughts.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
November 9, 2010 at 6:36 am
Thank you so much.
Product A and B have nothing in common except democraphic tables. A developer suggests put Product B in a seperated database so that he can use CodeSmith auto code generation on it . -- add/edit/delete table codes are generated.
All our web applications are accessed from 1 portal, so everything has to be in the same database.
November 22, 2010 at 8:24 am
Hi,
I think you should use a dedicated table for ProductLine. There may already be a natural key, that can be used as key, otherwise you could generate guid, as you do in the other tables.
It seems that a certain product can only be in in one productLine. In that case you could extend the Product tables.
So the tables would look like:
ProductA
ProductGUID (PK)
AccountGUID(FK)
ProductLineGUID(FK)
ProductLine
ProductLineGUID (PK)
... some other columns describing ProductLine
The FK constraint means of course that everything should be in one database, which I would say is a good idea anyway.
Good luck with your design!
Cheers
December 6, 2010 at 9:47 pm
Questions
A) Should I create a new database for product line B? A salesrep may work on both product line A and product line B, so as a user who may mantain 2 product lines. How to identify the same salesrep for the comissions of 2 products?
B) Or put product line B in the same database which would add 30 extra tables, that will be easier to work with those reusable tables.
Answers
Looking at your two questions, i would like to explain that databases are a physical collection of units. Its hard to find a simple topic across a whole book but its very easy to find in lessons. Which is splitting a collection will actually gives more easy and fast retrieval.
Considering your company grew up with some n number of products then you shouldnt keep it in same database which is more pain to your head. I would say please store your common tables in a separate database and product's own table in separate database. This will give more easy and fast retrieval.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply