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.
| Author |
Topic |
|
ann
Posting Yak Master
220 Posts |
 Posted - 2007-09-04 : 15:35:31
|
| I have an existing table - invoices. I need to create 2 new tables to hold vehicle and dealer information. An invoice can have one or more vehichles and or dealers. A vehicle can be related to a dealer one or more times, or not at all. A dealer can be related to a vehicle one or more times or not at all. Basically I have a invoice that can have one or more vehicles and\or dealers. I have no idea on how I should create the vehicle and dealer table. The end result I have to be able to query an invoice and show all vehicles with related dealers and also a second query that shows all vehicles and dealers that are not related to each other (based on invoice). Can anyone help me with this? I don't even know where to begin this and afraid I'll muck it up. Thanks |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-09-05 : 10:03:16
|
| What does your existing invoices table look like? Presumably it contains some information pertaining to dealers and vehicles, otherwise you're not going to be able to associate existing records with records in the new tables.Mark |
 |
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2007-09-05 : 13:50:46
|
| The invoice table looks something like this:InvoiceID, InvDate, InvNumber......The invoice table is the parent table. The new tables will be child tables, the relationship between the Invoice to the new tables (vehicle and dealers) will be one to many. The vehicle and dealers table are my problem - they are a many to many relationship. I have been reading up on these relationships, and what I gather that inbetween the many-to-many tables there needs to exist a link table containing the VehicleID and DealerID, the link table creates a one to many for vehicle/dealer and vice versa. I think I get that, so I would end up with something like this for my 2 new tables:Vehichle table:VehicleID, IvoiceID, Make, Model.....Dealer table:DealerID, IvoiceID, Name, Address....VehicleDealerLink talbe:VehicleDealerLinkID, VehicleID, DealerIDI'm not sure if this is correct? Does any one know? Also, if this is correct, how do I do a query the link table based on an invoice id? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-09-05 : 18:05:26
|
| You listed your entities, so we can start with 3 tables: Invoice, Dealer and Vehicle.Then you listed the relationships (good ;) ):An Invoice as One to Many Dealers An Invoice as One to Many VehiclesDealers can be associated with Zero or More Vehicles (and vise versa).So, then we can create the joining tables: InvoiceDealer, InvoiceVehicle and DealerVehicle.So the tables would look something like:Invoice: InvoiceID, InvoiceDate, InvoiceNumber, etc..Vehicle: VehicleID, Make, Model, etc..Dealer: DealerID, Name, Address, etc..InvoiceDealer: InvoiceID, DealerIDInvoiceVehicle: InvoiceID, VehicleIDDealerVehicle: DealerID, VechicleIDThis is a way to relate the data at the most basic level. However, what you didn’t mention is if you want the database to enforce any constraints. For example: Would you would the database to complain if you tried to create Invoice link to a Dealer/Vehicle if that a particular dealer does not supply said vehicle?-Ryan |
|
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2007-09-05 : 18:20:16
|
| Ryan - a dealer does not have to be associated with a vehicle, nor does a vehicle need to be associated with a dealer, they just both have to be associated with the invoice. In other words (this is really hard to explain, but I'll try), the dealer & vehicle can exist independantely of each other and also with the many-to-many relationship - is that what you are asking me?Mark- the invoice table is the parent table, the vehicle and dealer tables are new (under developement), so there is no vehicle or dealer info in the parent table. Since the invoice is parent, and the vehicle & dealer are children with the one to many relationship - why do I need to have any dealer or vehicle info in the invoice table? Am I missing something? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-09-05 : 18:32:18
|
| Ann, that is good to know. And, in that case, the basic schema I provided should do the trick. |
|
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2007-09-05 : 18:56:15
|
| Ryan - thanks for clearing that up for me ..... but, how do I query the data now (I am not strong in sql, just basic queries). I need to have 2 queries, 1 that gets all associated vehicle/dealers (these would be the ones that are related to each other in the vehicledealerlink table) and then any vehicle (dealer would be same) that are not in the vehicledealerlink table - (I would be passing in an invoiceID, since these queries relate directly to an invoice):Example an invoice would list all the vehicles that had associated dealers and then if there existed for that invoice any vehicle or dealers that are not in the vehicledealerlink, then I have to get those too.Sorry to ask all these questions - I used to work in a big place were db work was handled by a group that did only that - I am a programmer, so I didn't have to any db work. I am now in a much smaller place where I have to do everything....lol! scary :) |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-09-06 : 04:59:29
|
| ann, I think it would be helpful if you told us a bit about your business, and what exactly is being invoiced.Mark |
|
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2007-09-06 : 14:52:40
|
| Mark - this particular business unit develops referrals or "leads" if you want to other business units within the orginization pertaining to, in this case, auto loans. So when someone from this unit is speaking to someone about potentially purchasing a car, this information will be recorded in the db. The customer could be interested in one or more cars from a particular dealer, or has a car in mind, but not a dealer or the customer has visited several dealerships but unsure of a vehicle - or any number of variations to this theme, this information will be recorded in the db. An invoice is the parent table as all this information is referred to different units within the business - the invoice records the, other than basic invoice information, the details of the customers interest in any of the scenarios I described above. A referral fee is paid back to this business unit for each referral - hence the invoice.My task is to provide a front end to accomodate these vehicles/dealers to the "Invoice" (c#, asp.net) which includes not only data entry, but also front end to show the details to each invoice, which includes the invoice info, list any vehicles with associated dealers and list of vehicles and dealers not associated with each other. This is only a portion of what this program does, but my task is as outlined above.Using the schema Ryan provided, I've figured out the sp to get the vehicle/ dealer info that are not associated with each other. What I need to do, but can't figure out how to do, is write the sp to get the information for the vehicle/dealer info - the many-to-many relationship. |
|
|
|
ann
Posting Yak Master
220 Posts |
Posted - 2007-09-06 : 16:09:41
|
| Never mind, I figured out how to do what I need - thanks to Ryan & Mark for helping me |
|
|
|
|
|
|
|
|
Table design help