December 15, 2002 at 12:16 pm
Hi Folks,
I hope someone could help me out with this table design/relationship problem I am having.
First off I am using SQL 2000 as the database, asp/vb pages,using Macromedia's Ultradev 4 to develop the site. This is my first attempt at creating a dynamic website so I am very much a newbie which is winging it by learning what I can from various books I purchased and information found online. If you reply to my question it would be greatly appreciated if you do it in a format similar to explaining it to a "five" year old so I am sure to understand it.
Here is the scenario: "A website for connecting dog groomers with dog owners"
The dog owners locate the dog groomers by entering their zip/postal code as a query with brings up a list of dog groomers who service their area.
The dog groomers pay a annual membership or subscription fee to be listed on the website and must renew their membership annually.I will be using Clickbank as a billing provider. They provide a 8 digit number they call a ticket everytime a new dog groomer signs-up.
Now here are the tables I need and their relationships.
Table 1 (main table)
Name: DogGroomers
This table contains all contact and services information for the dog groomers
Primary key: DGuserID which is a auto generated Identity key
Table 2
Name: PostalCodes
This table contains only the dog groomers user ID and the zip/postal codes they service. Each dog groomer may service up to 10 zip/postal codes.
Foreign key: is DGuserID from the DogGroomers table
(I plan on indexing the postal code column to speed up searching)
Table 3 ( the one I am stuck on)
Name: Subscriptions
This table needs to contain subscription data including:
1) when original record was created (when dog groomer first siged up)
2) Clickbank Ticket number associated with subscription or subscription renewal(*see note below)
3) The subscription renewal date
4) Subscription expiry date
Primary key: ?
Foreign key: ?
This table has a one to one relationship with the DogGroomers table but I have read I can not use The DGuserID from the DogGroomers table because it is already being used as a foreign key in the PostalCodes table. Regretfully there is no other candidate at this time for a primary key in the DogGroomers table.
* note - the only way to confirm if the dog groomer has entered the correct ticket number is by comparing it to the sales stats provided online by Clickbank which is a long manual process.
Questions:
1) How should I create this table
The table must create a subscription expiry date by adding on 1 year from the original sign-up date. When the subscription is renewed the second year it must add on 2 years to the original sign-up date.I must keep track of the latest ticket number issued
2) what can I use as a foreign key to create the relationship to the correct user in the Dog groomers table
Now here are some of my business rules for the site which may help out.
1) The dog groomer will first be prompted to enter this ticket number and to choose a username and a password. (**see note below)
2) The dog groomer then fills in all contact information and service related information.
3) The dog groomer then fills in the postal codes he wants to service.
4) If a dog groomer has not renewed his subscription by the due date he needs to be deleted from all three tables.
Sorry for the lenght of this post but I wanted to make it as clear as possible hoping someone can help me out. If you can please keep your explanation as simple and complete as possible.
Thanks to all,
Robert
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!
December 16, 2002 at 4:36 am
I use SQL7 and have used keys on one table as foreign keys on several other tables. Where did you read 'I can not use The DGuserID from the DogGroomers table because it is already being used as a foreign key in the PostalCodes table'.
If table3 is one for one to table1 (and only ever will be) why not put table3 data on table1.
As for updates, I always use procs and would do renewal/expiry calc and update in proc.
Far away is close at hand in the images of elsewhere.
Anon.
December 16, 2002 at 5:09 pm
Thanks for the reply David,
I read it in a posting on a SQL related site. I am glad to hear the PK can be used in more than 1 table as a FK because this greatly simplifies my problem.
I want to keep the subscription table seperate from the other tables. Could you go into more detail on how you set-up your subscription system on your sites.
Thanks,
Rob
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!
December 18, 2002 at 2:32 am
Sorry but we do not have subscription systems so I cannot really help there. Whether you keep table3 separate is a matter of choice I suppose (I have a database where I wished I had done this).
I have created databases using FK's with single amd multiple columns.
Two things to note though
If the PK is multi column key then FK must reference all columns referenced by the PK. May sound obvious but I have fallen foul of this before.
A while ago, could have been 6.5, I could not add a trigger to a table with a FK. I don't know if this is still true. Maybe someone else could confirm.
Far away is close at hand in the images of elsewhere.
Anon.
December 18, 2002 at 7:13 am
I can confirm that you can use any column in a table as foreign key in other tables as much as you like. You cannot add two Primary keys to a single table, maybe that's the confusion in your case?
As for the database design, my personal view on this:
1. I would not create a separate table for keeping the postal codes. If you create an index on the columns, putting all information in a single table will not affect performance (not much anyway).
2. I would put the 'original sign up' date in the main table, since it is static information. And then, I would create a Subscriptions table that contains one record for each subscription (so not for each groomer). Why? If you do this, you will have some sort of history on what actions a groomer did. This might also help you when you have to determine the number of subscriptions/renewals a groomer has made using a 'simple' count.
So the design would consists of two tables, one with the static Groomer data and one with the dynamic subscription data.
And finally, I am wondering about the following scenario :
1. A groomer registers today (say the 18th of December 2002) and uses our service.
2. He decides not to renew his subscription next year for some reason.
3. But somewhere in January 2005 he does want to renew his subscription.
I probably missed something, but I'm not sure your current design supports this.
December 18, 2002 at 3:35 pm
David - Npeters thanks for the reply,
David - the FK is a single column so I won't have a problem there.
Npeters - The reason I want to create a separate postal code table is each of the dog groomers selects up to 10 postal/zip codes to indicate where he provides his services. The search by dog owners is made only on this field.
As far as the example of an expired subscription I have added a status column to the table so dog groomers who have allowed their subscription to expire will not show up in the search results.
Phone a Babysitter.com
Connecting Parents and Babysitters Nationwide!
December 19, 2002 at 5:34 am
If you link more postal codes to a single groomer, you will have to provide a separate table.
About the subscription stuff, I was referring more to the process for groomers than for owners. In theory you don't even need a status column, since you have the expiry date in the table. Won't hurt to add it though.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply