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 |
|
andrewgerm
Starting Member
3 Posts |
 Posted - 2007-10-11 : 14:06:26
|
| Hi allI have an application I'm developing. I need to capture items, and stores. Each item can be in many stores, and each store can have many different items. I guess the way to solve this would be an items table, and a stores table, and then have a table that records which stores have which items.Now each store will have certain details, including address location. There can be several stores in a particular location.When I add the details for a store, I obviously submit that to the stores database. I have each town stored in a seperate table, and linked via an id field to the stores table. When I submit a new store to the stores database, I want to check if the town exists already in the town table. If it does, then I want to add the new record to the stores database, and link the town via the id to the relevant field. If the town has not yet been entered into the town table, then it should be added, and from there, the new store should be added as previously.These are all done in a stored procedure, so I can only pass the town name, especially if it doesn't already exist. If it does, I'll need to look up the id, if it doesn't SQL needs to create the id (I'm using the sql identity).Any help, or suggestions would be greatly appreicated. I seem to have hit a bit of a dead end here.Thank you in advance.My code so far looks like:CREATE PROCEDURE UpdStore( @UpdStoreName varchar (20) = NULL, @UpdStoreAddress varchar(50) = NULL, @UpdStoreProvince varchar (4) = NULL, @UpdStoreSuburb varchar (4) = NULL, @UpdStoreTel varchar (10) = NULL)ASIF EXISTS(SELECT SuburbName FROM dbo.SuburbsList WHERE SuburbName = @UpdStoreSuburb) --This means it exists INSERT INTO dbo.Stores (@UpdStoreName, @UpdStoreAddress, @UpdStoreSuburb, @UpdStoreProvince, @UpdStoreTel)ELSE INSERT INTO dbo.SuburbList (@UpdStoreSuburb) INSERT INTO dbo.Stores (@UpdStoreName, @UpdStoreAddress, @UpdStoreSuburb, @UpdStoreProvince, @UpdStoreTel)GO |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 15:05:02
|
| "There can be several stores in a particular location"location = "Town" or a single store in a single building??If the first then you need something more unique than "Town" to differentiate them.If the second then you need way-more-control-data in your Schema.Kristen |
 |
|
|
andrewgerm
Starting Member
3 Posts |
Posted - 2007-10-11 : 15:11:42
|
| HiThanks for the reply.It will be several stores in a praticular town, not building. I have all stores in a table, with an auto id field to get a unique id, the same with each store item in a seperate table, and the same with towns. What I had thought of doing, was as each store is added, then the id for the town will go into the relevant field in the store database, and I'd use a join to the town table. But, if I submit a new store, and the submitted town name is not yet in the town table, then it should be added, and in doing so, will obtain a unique id, and then I want to add the rest of the information to the store table, along with the newly created town id.Thanks in advance :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 17:04:34
|
OK, but not quite sure what your question is now  "I submit a new store, and the submitted town name is not yet in the town table, then it should be added, and in doing so, will obtain a unique id"What about a Typo in the Town name - should that automatically create a new Town ID/record? (Might not be relevant in your scenario, but just thought I would ask ...Kristen |
|
|
|
andrewgerm
Starting Member
3 Posts |
Posted - 2007-10-11 : 18:19:58
|
| Oh :)Hadn't got to the typo's yet... For now, I guess those would be easy enough to spot manually. What I'll do once the table gets populated a bit more is to generate a list of possible towns, and allow the user to select one, or to enter a name if its not on the list.Ok, let me try rephrase.I have some tables. One holds Items, each identified by a sql generated id; I have a stores table, also each identified by a sql generated id, the same for towns. Each store will hold stock of certain items, one of a particular item, but it can have any combination of different items. There will be several stores, and several items. Each store will be located in a particular town, perhaps more than one store per town. Users will be capturing data initially, but also searching on it. When a user enters data for a store, they must include certain info, one piece being town. If the town they entered already exists in the town table, then we leave that table alone, and enter the id for the town into the appropriate field in the store database, as part of its location. The user will enter the town name, but we will need to update the new store record using the town id. If, however, the town does not exists, we need to capture the town name, have sql give it a unique id, and then proceed as usual by capturing the new store which will now have the newly created town record to use for its town id field.Searching will later be done by using another talbe to link each store to each item, as there is a many-to-many relationship here. Many items, of which a store will have any combination, and many stores, each of which can have any of the items. I created a table that will have two coloumns, one for store id, and one for item id (I haven't yet got to sorting that bit out much further though, as I guess this is kind of stretching my sql skills a bit <g>)Thank you for the replies so far. Hopefully I'll get all this puzzling solved :) |
|
|
|
|
|
|
|
|
Update 2 tables conditionally