|
 |
access thread: Table Schema
Message #1 by "Bob Bedell" <bobbedell15@m...> on Sat, 23 Nov 2002 22:42:11
|
|
Hi,
Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
Payment' entities (if thats a way to put it) in a table schema. I've been
playing with the following:
tblC = tblCustomers
tblO = tblOrders
tlkpP = tlkpPaymentMethods (lookup table)
tblC tblO tlkpP
tblO.OrderID
tblC.CustomerID 1--M tblO.CustomerID
tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
tblO.CreditCardNumber tlkpP.PaymentMethodDesc
Seems like I need more tables to fine tune this.
a) If I choose say, Cash, Check, or Money Order for a payment method, I
end up with a lot of null values in the CreditCardNumber field. Maybe
seperate tables for cash methods and credit methods? I'd like to avoid
the nulls.
b) There is a many-to-many relationship between tblCustomers and
tlkpPaymentMethods. tblOrders is doing double duty as the linking table.
Seems like I should have a seperate linking table. Maybe put the
Credit Card Numbers there?
c) If a customer selects a credit card as a method of payment, I want to
be able to display the credit cards they already have on file. With the
above schema, I could get that info. by querying the orders table, but
is that the way to go, or should credit cards and their corresponding
numbers be stored in another (a credit card?) table? Another many-to-
many relationship between Customers and Credtit Cards tables with a
linking table joining them?
I realize there are probably several answers, but it seems like there's
gotta be a tried and true 'canned' schema for this floating around
somewhere. Any thoughts on how folks set this up would be appraciated.
Thanks,
Bob
Message #2 by "Bob Bedell" <bobbedell15@m...> on Sun, 24 Nov 2002 05:04:07
|
|
I've also seen this kind of thing done using tlkpPaymentMethods as a
lookup for a payments table (tblPayments). tblPayments would have fields
like the following:
PaymentID
ProjectID (or OrderID)
PaymentAmount
PaymentDate
CreditCardNumber
CardholderName
CreditCardExpDate
PaymentMethodID
If payment method isn't a credti card, 3 of these fields will contain
Null values. I'm wondering if thats an indication that this table needs
further normalization, but I don't know how to do it. Or maybe all the
nulls are OK.
Also, lists of methods of payment often look like:
Cash
Check
Money Order
Visa
Master Card
American Express
Discover
But the credit card names are really a subset of the payment method
'Credit Card'. Maybe two tables are needed here:
tlkpPaymentTypes tlkpCreditCardTypes
Cash Visa
Check Master Card
Money Order American Express
Credit Card Discover
Just wondering.
Bob
> Hi,
> Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
P> ayment' entities (if thats a way to put it) in a table schema. I've
been
p> laying with the following:
> tblC = tblCustomers
t> blO = tblOrders
t> lkpP = tlkpPaymentMethods (lookup table)
> tblC tblO tlkpP
> tblO.OrderID
t> blC.CustomerID 1--M tblO.CustomerID
> tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
> tblO.CreditCardNumber tlkpP.PaymentMethodDesc
> Seems like I need more tables to fine tune this.
> a) If I choose say, Cash, Check, or Money Order for a payment method, I
> end up with a lot of null values in the CreditCardNumber field. Maybe
> seperate tables for cash methods and credit methods? I'd like to avoid
> the nulls.
> b) There is a many-to-many relationship between tblCustomers and
> tlkpPaymentMethods. tblOrders is doing double duty as the linking
table.
> Seems like I should have a seperate linking table. Maybe put the
> Credit Card Numbers there?
> c) If a customer selects a credit card as a method of payment, I want to
> be able to display the credit cards they already have on file. With
the
> above schema, I could get that info. by querying the orders table, but
> is that the way to go, or should credit cards and their corresponding
> numbers be stored in another (a credit card?) table? Another many-to-
> many relationship between Customers and Credtit Cards tables with a
> linking table joining them?
> I realize there are probably several answers, but it seems like there's
g> otta be a tried and true 'canned' schema for this floating around
s> omewhere. Any thoughts on how folks set this up would be appraciated.
> Thanks,
> Bob
>
Message #3 by "Charlie Goodwin" <cgoodwin@c...> on Sun, 24 Nov 2002 00:27:50 -0500
|
|
I'm not sure what is "best", but it seems that the possession of one or mor
e credit cards looks like a property of the customer.
The possibility of one customer owning either none or more than one credit
card suggests a separate credit card table with the customer ID matched aga
inst however many credit cards.
TblCreditCard
PayMethID Cust CCardNo Issuer
4 Cust5 CC1234etc Visa
5 Cust5 CC5678etc mc
6 Cust5 CC5432etc amex
7 Cust6 CC2466etc visa
8 Cust6 CC9854etc diners
I don't see any need to connect indirectly to the customer table. If the
CC numbers were treated as a property of the orders, then the CC numbers co
uld become harder to pull up if old orders get archived etc.
If CC numbers are linked directly to tblcustomer, then information stays ac
cessible.
Maybe you could cycle through whatever records match the customer ID to mak
e up a value list of whatever credit card numbers that customer possesses,
and then to that list add in cash, check, money order etc, to populate a co
mbo/listbox for the payment method field in your order transaction table.
For Cust5 the combo/list could display
CC1234etcVisa
CC5678etcmc
CC5432etcAmex
So, your TblOrders would have payment methods as a FK.
TblOrders:
CustomerID PayMethID Amount Date
5 6 $10.00 1/1/2002
What about cash , money order, check?
?Union a tiny table to the creditcards info, just using the fields requisit
e for a given customer?
PayMethID
1 Cash
2 MoneyOrder
3 Check
4 CC1234Visa
5 CC5678mc
6 CC5432amex
?1, 2, and 3 to be shown for all customers, but only their individually own
ed credit cars if any?
Does that make any sense??
Charlie
Hi, Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
Payment' entities (if thats a way to put it) in a table schema. I've been
playing with the following:
tblC =3D tblCustomers
tblO =3D tblOrders
tlkpP =3D tlkpPaymentMethods (lookup table)
tblC tblO tlkpP
tblO.OrderID
tblC.CustomerID 1--M tblO.CustomerID
tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
tblO.CreditCardNumber tlkpP.PaymentMethodDesc
Seems like I need more tables to fine tune this.
a) If I choose say, Cash, Check, or Money Order for a payment method, I
end up with a lot of null values in the CreditCardNumber field. Maybe
seperate tables for cash methods and credit methods? I'd like to avoid
the nulls.
b) There is a many-to-many relationship between tblCustomers and
tlkpPaymentMethods. tblOrders is doing double duty as the linking table.
Seems like I should have a seperate linking table. Maybe put the
Credit Card Numbers there?
c) If a customer selects a credit card as a method of payment, I want to
be able to display the credit cards they already have on file. With the
above schema, I could get that info. by querying the orders table, but
is that the way to go, or should credit cards and their corresponding
numbers be stored in another (a credit card?) table? Another many-to-
many relationship between Customers and Credtit Cards tables with a
linking table joining them?
I realize there are probably several answers, but it seems like there's
gotta be a tried and true 'canned' schema for this floating around
somewhere. Any thoughts on how folks set this up would be appraciated.
Thanks,
Bob
Message #4 by "Bob Bedell" <bobbedell15@m...> on Sun, 24 Nov 2002 15:46:38 +0000
|
|
Charlie,
Thanks for the time and the detailed response. I was thinking the
credit card details should be off on there own somewhere. In a
production application I guess they'd be off in there own file in
a username/secure directory on a secure server somewhere. But I
couldn't figure out how to get them back into a select list on an
order or payment screen. Looking forward to giving your ideas a try.
I'm wondering if the realtionship between cards and customers is really
a many-to-many relationship. One customer can hold a Visa, MC, and AX,
but a Visa can be held by Cust1, Cust2, and Cust3. TblCreditCard
below would still look the same, but 'Issuer' would be a foreign key.
Seems like the primary key of TblCreditCard would need to be a
composite of all four fields to ensure unique records.
Thanks again,
Bob
>From: "Charlie Goodwin" <cgoodwin@c...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] re:Table Schema
>Date: Sun, 24 Nov 2002 00:27:50 -0500
>
>I'm not sure what is "best", but it seems that the possession of one or
>more credit cards looks like a property of the customer.
>
>The possibility of one customer owning either none or more than one credit
>card suggests a separate credit card table with the customer ID matched
>against however many credit cards.
>
>TblCreditCard
>PayMethID Cust CCardNo Issuer
>4 Cust5 CC1234etc Visa
>5 Cust5 CC5678etc mc
>6 Cust5 CC5432etc amex
>7 Cust6 CC2466etc visa
>8 Cust6 CC9854etc diners
>
>I don't see any need to connect indirectly to the customer table. If the
>CC numbers were treated as a property of the orders, then the CC numbers
>could become harder to pull up if old orders get archived etc.
>
>If CC numbers are linked directly to tblcustomer, then information stays
>accessible.
>
>Maybe you could cycle through whatever records match the customer ID to
>make up a value list of whatever credit card numbers that customer
>possesses, and then to that list add in cash, check, money order etc, to
>populate a combo/listbox for the payment method field in your order
>transaction table.
>
>For Cust5 the combo/list could display
>CC1234etcVisa
>CC5678etcmc
>CC5432etcAmex
>
>So, your TblOrders would have payment methods as a FK.
>
>TblOrders:
>CustomerID PayMethID Amount Date
>5 6 $10.00 1/1/2002
>
>What about cash , money order, check?
>
>?Union a tiny table to the creditcards info, just using the fields
>requisite for a given customer?
>PayMethID
>1 Cash
>2 MoneyOrder
>3 Check
>4 CC1234Visa
>5 CC5678mc
>6 CC5432amex
>
>?1, 2, and 3 to be shown for all customers, but only their individually
>owned credit cars if any?
>
>Does that make any sense??
>
>Charlie
>
>
>
>
>
>
>
>Hi, Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
>Payment' entities (if thats a way to put it) in a table schema. I've been
>playing with the following:
>
>tblC = tblCustomers
>tblO = tblOrders
>tlkpP = tlkpPaymentMethods (lookup table)
>
>tblC tblO tlkpP
>
> tblO.OrderID
>tblC.CustomerID 1--M tblO.CustomerID
> tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
> tblO.CreditCardNumber tlkpP.PaymentMethodDesc
>
>Seems like I need more tables to fine tune this.
>
>a) If I choose say, Cash, Check, or Money Order for a payment method, I
> end up with a lot of null values in the CreditCardNumber field. Maybe
> seperate tables for cash methods and credit methods? I'd like to avoid
> the nulls.
>
>b) There is a many-to-many relationship between tblCustomers and
> tlkpPaymentMethods. tblOrders is doing double duty as the linking
>table.
> Seems like I should have a seperate linking table. Maybe put the
> Credit Card Numbers there?
>
>c) If a customer selects a credit card as a method of payment, I want to
> be able to display the credit cards they already have on file. With the
> above schema, I could get that info. by querying the orders table, but
> is that the way to go, or should credit cards and their corresponding
> numbers be stored in another (a credit card?) table? Another many-to-
> many relationship between Customers and Credtit Cards tables with a
> linking table joining them?
>
>I realize there are probably several answers, but it seems like there's
>gotta be a tried and true 'canned' schema for this floating around
>somewhere. Any thoughts on how folks set this up would be appraciated.
>
>Thanks,
>
>Bob
>
>
>
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
Message #5 by "Bob Bedell" <bobbedell15@m...> on Sun, 24 Nov 2002 16:08:17 +0000
|
|
Hi Charlies,
<<the primary key of TblCreditCard would need to be a
composite of all four fields to ensure unique records>>
Is that right, or am I not thinking about that clearly. Also,
regarding:
<<Union a tiny table to the creditcards info>>
I guess this table would just be a "free-floating" list of values that
doesn't share relatioinships with other tables. Shoudl I relate it
to any other tables?
Thanks again for your time,
Bob
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] re:Table Schema
>Date: Sun, 24 Nov 2002 15:46:38 +0000
>
>Charlie,
>
>Thanks for the time and the detailed response. I was thinking the
>credit card details should be off on there own somewhere. In a
>production application I guess they'd be off in there own file in
>a username/secure directory on a secure server somewhere. But I
>couldn't figure out how to get them back into a select list on an
>order or payment screen. Looking forward to giving your ideas a try.
>
>I'm wondering if the realtionship between cards and customers is really
>a many-to-many relationship. One customer can hold a Visa, MC, and AX,
>but a Visa can be held by Cust1, Cust2, and Cust3. TblCreditCard
>below would still look the same, but 'Issuer' would be a foreign key.
>Seems like the primary key of TblCreditCard would need to be a
>composite of all four fields to ensure unique records.
>
>Thanks again,
>
>Bob
>
>>From: "Charlie Goodwin" <cgoodwin@c...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] re:Table Schema
>>Date: Sun, 24 Nov 2002 00:27:50 -0500
>>
>>I'm not sure what is "best", but it seems that the possession of one or
>>more credit cards looks like a property of the customer.
>>
>>The possibility of one customer owning either none or more than one credit
>>card suggests a separate credit card table with the customer ID matched
>>against however many credit cards.
>>
>>TblCreditCard
>>PayMethID Cust CCardNo Issuer
>>4 Cust5 CC1234etc Visa
>>5 Cust5 CC5678etc mc
>>6 Cust5 CC5432etc amex
>>7 Cust6 CC2466etc visa
>>8 Cust6 CC9854etc diners
>>
>>I don't see any need to connect indirectly to the customer table. If the
>>CC numbers were treated as a property of the orders, then the CC numbers
>>could become harder to pull up if old orders get archived etc.
>>
>>If CC numbers are linked directly to tblcustomer, then information stays
>>accessible.
>>
>>Maybe you could cycle through whatever records match the customer ID to
>>make up a value list of whatever credit card numbers that customer
>>possesses, and then to that list add in cash, check, money order etc, to
>>populate a combo/listbox for the payment method field in your order
>>transaction table.
>>
>>For Cust5 the combo/list could display
>>CC1234etcVisa
>>CC5678etcmc
>>CC5432etcAmex
>>
>>So, your TblOrders would have payment methods as a FK.
>>
>>TblOrders:
>>CustomerID PayMethID Amount Date
>>5 6 $10.00 1/1/2002
>>
>>What about cash , money order, check?
>>
>>?Union a tiny table to the creditcards info, just using the fields
>>requisite for a given customer?
>>PayMethID
>>1 Cash
>>2 MoneyOrder
>>3 Check
>>4 CC1234Visa
>>5 CC5678mc
>>6 CC5432amex
>>
>>?1, 2, and 3 to be shown for all customers, but only their individually
>>owned credit cars if any?
>>
>>Does that make any sense??
>>
>>Charlie
>>
>>
>>
>>
>>
>>
>>
>>Hi, Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
>>Payment' entities (if thats a way to put it) in a table schema. I've been
>>playing with the following:
>>
>>tblC = tblCustomers
>>tblO = tblOrders
>>tlkpP = tlkpPaymentMethods (lookup table)
>>
>>tblC tblO tlkpP
>>
>> tblO.OrderID
>>tblC.CustomerID 1--M tblO.CustomerID
>> tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
>> tblO.CreditCardNumber tlkpP.PaymentMethodDesc
>>
>>Seems like I need more tables to fine tune this.
>>
>>a) If I choose say, Cash, Check, or Money Order for a payment method, I
>> end up with a lot of null values in the CreditCardNumber field. Maybe
>> seperate tables for cash methods and credit methods? I'd like to avoid
>> the nulls.
>>
>>b) There is a many-to-many relationship between tblCustomers and
>> tlkpPaymentMethods. tblOrders is doing double duty as the linking
>>table.
>> Seems like I should have a seperate linking table. Maybe put the
>> Credit Card Numbers there?
>>
>>c) If a customer selects a credit card as a method of payment, I want to
>> be able to display the credit cards they already have on file. With
>>the
>> above schema, I could get that info. by querying the orders table, but
>> is that the way to go, or should credit cards and their corresponding
>> numbers be stored in another (a credit card?) table? Another many-to-
>> many relationship between Customers and Credtit Cards tables with a
>> linking table joining them?
>>
>>I realize there are probably several answers, but it seems like there's
>>gotta be a tried and true 'canned' schema for this floating around
>>somewhere. Any thoughts on how folks set this up would be appraciated.
>>
>>Thanks,
>>
>>Bob
>>
>>
>>
>
>
>_________________________________________________________________
>The new MSN 8: advanced junk mail protection and 2 months FREE*
>http://join.msn.com/?page=features/junkmail
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
Message #6 by "Bob Bedell" <bobbedell15@m...> on Sun, 24 Nov 2002 20:28:41 +0000
|
|
<<the primary key of TblCreditCard would need to be a
composite of all four fields to ensure unique records>>
Is that what multiple-field indexes are for? So for my TblCreditCard,
would I use PayMethID as the primary key, and then create a unique
index called, say CCardDetails, that includes Cust, CCardNo, and Issuer.
I don't kow a lot about indexes. I've usually just heard that they
are used to speed up sorting. But is using them in this was to prevent
record duplication another of their purposes? Using a primary key with
lots of fields seems a little odd.
Bob
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] re:Table Schema
>Date: Sun, 24 Nov 2002 16:08:17 +0000
>
>Hi Charlies,
>
><<the primary key of TblCreditCard would need to be a
>composite of all four fields to ensure unique records>>
>
>Is that right, or am I not thinking about that clearly. Also,
>regarding:
>
><<Union a tiny table to the creditcards info>>
>
>I guess this table would just be a "free-floating" list of values that
>doesn't share relatioinships with other tables. Shoudl I relate it
>to any other tables?
>
>Thanks again for your time,
>
>Bob
>
>
>>From: "Bob Bedell" <bobbedell15@m...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] re:Table Schema
>>Date: Sun, 24 Nov 2002 15:46:38 +0000
>>
>>Charlie,
>>
>>Thanks for the time and the detailed response. I was thinking the
>>credit card details should be off on there own somewhere. In a
>>production application I guess they'd be off in there own file in
>>a username/secure directory on a secure server somewhere. But I
>>couldn't figure out how to get them back into a select list on an
>>order or payment screen. Looking forward to giving your ideas a try.
>>
>>I'm wondering if the realtionship between cards and customers is really
>>a many-to-many relationship. One customer can hold a Visa, MC, and AX,
>>but a Visa can be held by Cust1, Cust2, and Cust3. TblCreditCard
>>below would still look the same, but 'Issuer' would be a foreign key.
>>Seems like the primary key of TblCreditCard would need to be a
>>composite of all four fields to ensure unique records.
>>
>>Thanks again,
>>
>>Bob
>>
>>>From: "Charlie Goodwin" <cgoodwin@c...>
>>>Reply-To: "Access" <access@p...>
>>>To: "Access" <access@p...>
>>>Subject: [access] re:Table Schema
>>>Date: Sun, 24 Nov 2002 00:27:50 -0500
>>>
>>>I'm not sure what is "best", but it seems that the possession of one or
>>>more credit cards looks like a property of the customer.
>>>
>>>The possibility of one customer owning either none or more than one
>>>credit card suggests a separate credit card table with the customer ID
>>>matched against however many credit cards.
>>>
>>>TblCreditCard
>>>PayMethID Cust CCardNo Issuer
>>>4 Cust5 CC1234etc Visa
>>>5 Cust5 CC5678etc mc
>>>6 Cust5 CC5432etc amex
>>>7 Cust6 CC2466etc visa
>>>8 Cust6 CC9854etc diners
>>>
>>>I don't see any need to connect indirectly to the customer table. If
>>>the CC numbers were treated as a property of the orders, then the CC
>>>numbers could become harder to pull up if old orders get archived etc.
>>>
>>>If CC numbers are linked directly to tblcustomer, then information stays
>>>accessible.
>>>
>>>Maybe you could cycle through whatever records match the customer ID to
>>>make up a value list of whatever credit card numbers that customer
>>>possesses, and then to that list add in cash, check, money order etc, to
>>>populate a combo/listbox for the payment method field in your order
>>>transaction table.
>>>
>>>For Cust5 the combo/list could display
>>>CC1234etcVisa
>>>CC5678etcmc
>>>CC5432etcAmex
>>>
>>>So, your TblOrders would have payment methods as a FK.
>>>
>>>TblOrders:
>>>CustomerID PayMethID Amount Date
>>>5 6 $10.00 1/1/2002
>>>
>>>What about cash , money order, check?
>>>
>>>?Union a tiny table to the creditcards info, just using the fields
>>>requisite for a given customer?
>>>PayMethID
>>>1 Cash
>>>2 MoneyOrder
>>>3 Check
>>>4 CC1234Visa
>>>5 CC5678mc
>>>6 CC5432amex
>>>
>>>?1, 2, and 3 to be shown for all customers, but only their individually
>>>owned credit cars if any?
>>>
>>>Does that make any sense??
>>>
>>>Charlie
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>Hi, Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
>>>Payment' entities (if thats a way to put it) in a table schema. I've been
>>>playing with the following:
>>>
>>>tblC = tblCustomers
>>>tblO = tblOrders
>>>tlkpP = tlkpPaymentMethods (lookup table)
>>>
>>>tblC tblO tlkpP
>>>
>>> tblO.OrderID
>>>tblC.CustomerID 1--M tblO.CustomerID
>>> tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
>>> tblO.CreditCardNumber tlkpP.PaymentMethodDesc
>>>
>>>Seems like I need more tables to fine tune this.
>>>
>>>a) If I choose say, Cash, Check, or Money Order for a payment method, I
>>> end up with a lot of null values in the CreditCardNumber field. Maybe
>>> seperate tables for cash methods and credit methods? I'd like to
>>>avoid
>>> the nulls.
>>>
>>>b) There is a many-to-many relationship between tblCustomers and
>>> tlkpPaymentMethods. tblOrders is doing double duty as the linking
>>>table.
>>> Seems like I should have a seperate linking table. Maybe put the
>>> Credit Card Numbers there?
>>>
>>>c) If a customer selects a credit card as a method of payment, I want to
>>> be able to display the credit cards they already have on file. With
>>>the
>>> above schema, I could get that info. by querying the orders table,
>>>but
>>> is that the way to go, or should credit cards and their corresponding
>>> numbers be stored in another (a credit card?) table? Another many-to-
>>> many relationship between Customers and Credtit Cards tables with a
>>> linking table joining them?
>>>
>>>I realize there are probably several answers, but it seems like there's
>>>gotta be a tried and true 'canned' schema for this floating around
>>>somewhere. Any thoughts on how folks set this up would be appraciated.
>>>
>>>Thanks,
>>>
>>>Bob
>>>
>>>
>>>
>>
>>
>>_________________________________________________________________
>>The new MSN 8: advanced junk mail protection and 2 months FREE*
>>http://join.msn.com/?page=features/junkmail
>>
>>
>>---
>>Change your mail options at http://p2p.wrox.com/manager.asp or to
>>unsubscribe send a blank email to
>
>
>_________________________________________________________________
>MSN 8 with e-mail virus protection service: 2 months FREE*
>http://join.msn.com/?page=features/virus
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
Message #7 by "Enzo Zaragoza" <enzaux@g...> on Mon, 25 Nov 2002 09:41:21 +0800
|
|
My suggestion is all Credit Card Info must be in the tblCustomers now if
a customer might have a possibility to have one or more credit cards then creating
another table for Credit Card Info would not be bad.
tblC tblO tlkpP
tblO.OrderID
tblC.CustomerID 1--M tblO.CustomerID
tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
tlkpP.PaymentMethodDesc
tblC tblCC(CreditCard)
tblC.CustomerID 1--M tblCC.CustomerID
tblCC.CreditID
If the user choose Credit as its payment method then make cboCreditCard enabled (if you
have one) and populate it with the credit card for the specified users.
This is only a suggestion and I don't know also if this is a good way to handle the
situation
Enzo c",)
-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Saturday, November 23, 2002 10:42 PM
To: Access
Subject: [access] Table Schema
Hi,
Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
Payment' entities (if thats a way to put it) in a table schema. I've been
playing with the following:
tblC = tblCustomers
tblO = tblOrders
tlkpP = tlkpPaymentMethods (lookup table)
tblC tblO tlkpP
tblO.OrderID
tblC.CustomerID 1--M tblO.CustomerID
tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
tblO.CreditCardNumber tlkpP.PaymentMethodDesc
Seems like I need more tables to fine tune this.
a) If I choose say, Cash, Check, or Money Order for a payment method, I
end up with a lot of null values in the CreditCardNumber field. Maybe
seperate tables for cash methods and credit methods? I'd like to avoid
the nulls.
b) There is a many-to-many relationship between tblCustomers and
tlkpPaymentMethods. tblOrders is doing double duty as the linking table.
Seems like I should have a seperate linking table. Maybe put the
Credit Card Numbers there?
c) If a customer selects a credit card as a method of payment, I want to
be able to display the credit cards they already have on file. With the
above schema, I could get that info. by querying the orders table, but
is that the way to go, or should credit cards and their corresponding
numbers be stored in another (a credit card?) table? Another many-to-
many relationship between Customers and Credtit Cards tables with a
linking table joining them?
I realize there are probably several answers, but it seems like there's
gotta be a tried and true 'canned' schema for this floating around
somewhere. Any thoughts on how folks set this up would be appraciated.
Thanks,
Bob
Message #8 by "Charlie Goodwin" <cgoodwin@c...> on Mon, 25 Nov 2002 00:23:25 -0500
|
|
Bob,
> Is that what multiple-field indexes are for?>
Multiple field indexes allow you to bypass limitations of single field inde
xes, if I get it right.
With single field it would be easy to create the situation where each conta
ct could have only one (or no) credit card, if uniqueness is enforced on ea
ch of both fields individually.
With an index using multiple fields, any contact could have many different
cards. But you could prevent multiple records for the same contact having
the same credit card. You can also prevent multiple contacts from using
the same credit card.
Mainly you can keep from having multiple records of a customer with the sam
e credit card, while still allowing that customer to have multiple differin
g credit cards.
I don't know about composite primary keys. Autonumbers seem to take care
of making unique IDs for each record pretty well. Why not keep indexing f
or "contents" of the table in separate fields, and let the PK be only a sim
ple unique and record identifier?
Maybe in the days when every bit saved was an urgent requirement, a composi
te Primary Key might save on field's space usage, by eliminating a separate
field for an autonumber or whatever, but now???
Indexes also are used to speed up sorting - and - to allow control of dupli
cate entries, so they work for multiple purposes.
> So for my TblCreditCard, would I use PayMethID as the primary key>
I'd go for an autonumber or the equivalent as PK for each record. Then I
would have fields for CCNum and ContactID, and any other info you may need
for each Cust/CreditCard record, such as expiration date.
Googling to : http://faqs.jmas.co.jp/FAQs/consumer-credit-faq/part3, it a
ppears that the issuer's ID is coded into the 16 digit number. "Issuer" w
ould be verboten as a separate field if one really lived by the rules of no
rmallization. Does anyone really really normalize?
Quoting from them:
"The first digit is the system:
3=3DT&E cards
4=3DVisa
5=3DMasterCard
6=3DDiscover. The structure of the card number varies by system:
American Express starts with 37;
Carte Blanche and Diners Club with 38."
There's much more in there, but for our purposes, you can treat an issuer f
ield as a optional convenience.
> ><<Union a tiny table to the creditcards info>>
> >
> >I guess this table would just be a "free-floating" list of values that
> >doesn't share relatioinships with other tables. Shoudl I relate it
> >to any other tables?
I'm not sure I'm the poster child for doing things correctly in a DB, and I
keep running into issues that remind me what a newby I am, but for what it
's worth, I create only some of the most direct and iron clad relationships
in the relationships window. I build all other connections in the query
window. For a little table like I propose,
ID Item
1 Cash
2 Money Order
3 Check
It's basic function is simply to allow you to have Cash,Money Order, Check
be also available as payment methods for each customer, wherever business r
ules allow, alongside whatever credit card numbers the customer has given.
I'm not sure a relationship with anything else in the DB makes sense, other
than when unioned with credit card numbers, or concatenated into a value l
ist of same, to offer the choices for paying off an order. I'd really be
interested to hear other Db people on how to do this. I think it should w
ork, and probably very well, but it seems somehow like cloning a terrier's
butt onto a horse's front. Anyone else out there?
> >> I was thinking the
> >>credit card details should be off on there own somewhere. In a
> >>production application I guess they'd be off in there own file in
> >>a username/secure directory on a secure server somewhere. But I
> >>couldn't figure out how to get them back into a select list on an
> >>order or payment screen. > >>
I can't answer about how to secure info, but I wouldn't be surprised if sep
arating off credit card numbers all by themselves presents least problems s
ecuring them.
> >>I'm wondering if the realtionship between cards and customers is really
> >>a many-to-many relationship. One customer can hold a Visa, MC, and AX,
> >>but a Visa can be held by Cust1, Cust2, and Cust3. TblCreditCard
> >>below would still look the same, but 'Issuer' would be a foreign key.
> >>Seems like the primary key of TblCreditCard would need to be a
> >>composite of all four fields to ensure unique records.> >>
As above, I'm not sure you absolutely need a separate field for "issuer".
I think the relationship any record would have with a customer would be pre
tty straightforward.
If you allow multiple customers to use the exact same card (say husband and
wife are recorded as separate customers and they use the same card number)
, then they would have separate records. To avoid possible issues with ex
piration dates, with multiple users of individual card numbers, I might spl
it off a second table
TblCreditCardsAndUserMatchupTableYouCanComeUpWithAShorterName
RecordID CustomerID CreditCardID
100 5 22
101 10 22
CreditCardID CreditCardNumber Expiration
22 1234 1234 1234 1234 1/1/2003
34 1111 2222 3333 4444 2/2/2004
etc
Then there would be just one entry point for updating expirations.
I think that should keep it a workable set of one to many relationships rat
her than many to many.
Hope these thoughts have some use.
Charlie
> >>>I'm not sure what is "best", but it seems that the possession of one or
> >>>more credit cards looks like a property of the customer.
> >>>
> >>>The possibility of one customer owning either none or more than one
> >>>credit card suggests a separate credit card table with the customer ID
> >>>matched against however many credit cards.
> >>>
> >>>TblCreditCard
> >>>PayMethID Cust CCardNo Issuer
> >>>4 Cust5 CC1234etc Visa
> >>>5 Cust5 CC5678etc mc
> >>>6 Cust5 CC5432etc amex
> >>>7 Cust6 CC2466etc visa
> >>>8 Cust6 CC9854etc diners
> >>>
> >>>I don't see any need to connect indirectly to the customer table. If
> >>>the CC numbers were treated as a property of the orders, then the CC
> >>>numbers could become harder to pull up if old orders get archived etc.
> >>>
> >>>If CC numbers are linked directly to tblcustomer, then information sta
ys
> >>>accessible.
> >>>
> >>>Maybe you could cycle through whatever records match the customer ID to
> >>>make up a value list of whatever credit card numbers that customer
> >>>possesses, and then to that list add in cash, check, money order etc,
to
> >>>populate a combo/listbox for the payment method field in your order
> >>>transaction table.
> >>>
> >>>For Cust5 the combo/list could display
> >>>CC1234etcVisa
> >>>CC5678etcmc
> >>>CC5432etcAmex
> >>>
> >>>So, your TblOrders would have payment methods as a FK.
> >>>
> >>>TblOrders:
> >>>CustomerID PayMethID Amount Date
> >>>5 6 $10.00 1/1/2002
> >>>
> >>>What about cash , money order, check?
> >>>
> >>>?Union a tiny table to the creditcards info, just using the fields
> >>>requisite for a given customer?
> >>>PayMethID
> >>>1 Cash
> >>>2 MoneyOrder
> >>>3 Check
> >>>4 CC1234Visa
> >>>5 CC5678mc
> >>>6 CC5432amex
> >>>
> >>>?1, 2, and 3 to be shown for all customers, but only their individually
> >>>owned credit cars if any?
> >>>
> >>>Does that make any sense??
> >>>
> >>>Charlie
> >>>Hi, Whats a "best" way to model the 'Customers', 'Orders' and 'Methods
of
> >>>Payment' entities (if thats a way to put it) in a table schema. I've b
een
> >>>playing with the following:
> >>>
> >>>tblC =3D tblCustomers
> >>>tblO =3D tblOrders
> >>>tlkpP =3D tlkpPaymentMethods (lookup table)
> >>>
> >>>tblC tblO tlkpP
> >>>
> >>> tblO.OrderID
> >>>tblC.CustomerID 1--M tblO.CustomerID
> >>> tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
> >>> tblO.CreditCardNumber tlkpP.PaymentMethodD
esc
> >>>
> >>>Seems like I need more tables to fine tune this.
> >>>
> >>>a) If I choose say, Cash, Check, or Money Order for a payment method, I
> >>> end up with a lot of null values in the CreditCardNumber field. Ma
ybe
> >>> seperate tables for cash methods and credit methods? I'd like to
> >>>avoid
> >>> the nulls.
> >>>
> >>>b) There is a many-to-many relationship between tblCustomers and
> >>> tlkpPaymentMethods. tblOrders is doing double duty as the linking
> >>>table.
> >>> Seems like I should have a seperate linking table. Maybe put the
> >>> Credit Card Numbers there?
> >>>
> >>>c) If a customer selects a credit card as a method of payment, I want
to
> >>> be able to display the credit cards they already have on file. With
> >>>the
> >>> above schema, I could get that info. by querying the orders table,
> >>>but
> >>> is that the way to go, or should credit cards and their correspond
ing
> >>> numbers be stored in another (a credit card?) table? Another many-
to-
> >>> many relationship between Customers and Credtit Cards tables with a
> >>> linking table joining them?
> >>>
> >>>I realize there are probably several answers, but it seems like there's
> >>>gotta be a tried and true 'canned' schema for this floating around
> >>>somewhere. Any thoughts on how folks set this up would be appraciated.
> >>>
> >>>Thanks,
> >>>
> >>>Bob
Message #9 by "Carnley, Dave" <dcarnley@a...> on Mon, 25 Nov 2002 10:31:16 -0600
|
|
Good db design comes form abstract thinking about your system. So look at
what entities you need to track and their relationships, then the answers
might become more clear. Trying to anticipate screen displays and
performance issues at the point of designing your entities can lead to
problems...
So, with that said...
I would probably use a Customers table, a CustomerCreditCard table, a
PaymentMethod table and an Order table.
The order would have foreign keys to the customer, payment method, and the
customer credit card record. the payment method lists only the types :
cash, check, mo, credit card. The Customer credit card key would be null
when payment method <> credit card. I don't think there is a need to list
each card brand in the payment method table, that can be part of the
customer credit card table for each particular card. You could make another
table that list the available values for the credit card issuer (see (b)
below)...
to address the concerns you listed :
(a) you are concerned about null cc#s on orders that do not use credit
cards. In the design I laid out, you would still have null fields for
orders where the customer did not use a cc, however every order would have a
value for payment method. I don't think this is a problem however since the
pay method will indicate which records should have a cc value and which
should be null. There is no penalty for having null fields!
(b) yes, I guess you could say that there is a many-many between payment
methods and customers, if you think about a payment method as an Entity.
However if in your mind you reclassify the list of payment methods as a
domain of values for the payment method property on an order, then you can
see that the need for a M:N cross reference is really not needed. If, for
example, at some point you need a list of customers who have used cash, this
can be easily queried from the orders table. Is there any reason to store a
table that lists every customer id as able to use cash to pay for orders? A
customer's credit card is a real thing, and so can merit it's own entity
(and therefore table). A list of values allowed in a field of an entity is
not necessarily an entity itself, but sometimes it is.
(c) this should be obvious but providing a list of the customers cc numbers
is pretty easy when you have a table that lists customer cc's. When a
customer adds a new card or cancels an old one the customer cc table makes
this all very easy...
I hope this helps some...
-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Saturday, November 23, 2002 4:42 PM
To: Access
Subject: [access] Table Schema
Hi,
Whats a "best" way to model the 'Customers', 'Orders' and 'Methods of
Payment' entities (if thats a way to put it) in a table schema. I've been
playing with the following:
tblC = tblCustomers
tblO = tblOrders
tlkpP = tlkpPaymentMethods (lookup table)
tblC tblO tlkpP
tblO.OrderID
tblC.CustomerID 1--M tblO.CustomerID
tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
tblO.CreditCardNumber tlkpP.PaymentMethodDesc
Seems like I need more tables to fine tune this.
a) If I choose say, Cash, Check, or Money Order for a payment method, I
end up with a lot of null values in the CreditCardNumber field. Maybe
seperate tables for cash methods and credit methods? I'd like to avoid
the nulls.
b) There is a many-to-many relationship between tblCustomers and
tlkpPaymentMethods. tblOrders is doing double duty as the linking table.
Seems like I should have a seperate linking table. Maybe put the
Credit Card Numbers there?
c) If a customer selects a credit card as a method of payment, I want to
be able to display the credit cards they already have on file. With the
above schema, I could get that info. by querying the orders table, but
is that the way to go, or should credit cards and their corresponding
numbers be stored in another (a credit card?) table? Another many-to-
many relationship between Customers and Credtit Cards tables with a
linking table joining them?
I realize there are probably several answers, but it seems like there's
gotta be a tried and true 'canned' schema for this floating around
somewhere. Any thoughts on how folks set this up would be appraciated.
Thanks,
Bob
Message #10 by "Bob Bedell" <Bob Bedell> on Tue, 26 Nov 2002 03:10:31
|
|
Charlie, Dave, and Enzo:
Wow, thanks for the thoughtful responses! Thats a lot to work with.
Before I get going though, I have a further question. One of Charlie's
comments kinda' crystalized it:
<<Why not keep indexing for "contents" of the table in separate fields,
and let the PK be only a simple unique and record identifier?>>
The two schema that follow are, as far as I can tell, functionally
equivalent:
PK = Primary Key
FK = Foreign Key
CPK/FK COmposite Primary Key/Foreign Key
tblOrders trelOrderDetails tblProducts
tblO.OrderID
tblO.CustomerID
tblO.PaymentMethodID M--1 tlkpP.PaymentMethodID
tblO.CreditCardNumber tlkpP.PaymentMethodDesc
Message #11 by "Bob Bedell" <bobbedell15@m...> on Tue, 26 Nov 2002 03:40:41
|
|
ooops...fat-fingered that one...what I meant to say was...
The two schema that follow are, as far as I can tell, functionally
equivalent:
PK = Primary Key
FK = Foreign Key
CPK/FK = Composite Primary Key/Foreign Key
1).
tblOrders trelOrderDetails tblProducts
OrderID(PK) 1--M OrderID(CPK/FK)
ProductID(CPK/FK) M--1 ProductID(PK)
Unique Indexes: Table Index Name Fields
tblOrders Primary Key OrderID
tblProducts Primary Key ProductID
trelOrderDetails Primary Key OrderID
ProductID
2).
tblOrders trelOrderDetails tblProducts
OrderDetailID(PK)
OrderID(PK) 1--M OrderID(FK)
ProductID(FK) M--1 ProductD(PK)
Unique Indexes: Table Index Name Fields
tblOrders Primary Key OrderID
tblProducts Primary Key ProductID
trelOrderDetails Primary Key OrderDetailsID
OrderDetails OrderID
ProductID
The first uses a composite primary key; the second, a single primary key
and a unique index consisting of the two foreign keys. Referential
integrity is enforced in both cases.
Are there any non-trivial differences between these two schema? Both seem
fairly common. Are there reasons to prefer one to the other?
Message #12 by "Carnley, Dave" <dcarnley@a...> on Tue, 26 Nov 2002 09:35:34 -0600
|
|
The only difference I can see is that, on insert to the orderdetails table,
in the second schema, you might save a tick or two of extra overhead
depending on how you cluster the indices. But that is probably negligible.
You might find it a little easier when writing your application to work with
order detail records that have a single field key than records that have
composite keys - for example if you use OO techniques, it is a no-brainer to
use the record key as the collection key; but if the record key is two
fields then you have to concatenate/split the values when going from db to
collection and back.
-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: Monday, November 25, 2002 9:41 PM
To: Access
Subject: [access] re:Table Schema
ooops...fat-fingered that one...what I meant to say was...
The two schema that follow are, as far as I can tell, functionally
equivalent:
PK = Primary Key
FK = Foreign Key
CPK/FK = Composite Primary Key/Foreign Key
1).
tblOrders trelOrderDetails tblProducts
OrderID(PK) 1--M OrderID(CPK/FK)
ProductID(CPK/FK) M--1 ProductID(PK)
Unique Indexes: Table Index Name Fields
tblOrders Primary Key OrderID
tblProducts Primary Key ProductID
trelOrderDetails Primary Key OrderID
ProductID
2).
tblOrders trelOrderDetails tblProducts
OrderDetailID(PK)
OrderID(PK) 1--M OrderID(FK)
ProductID(FK) M--1 ProductD(PK)
Unique Indexes: Table Index Name Fields
tblOrders Primary Key OrderID
tblProducts Primary Key ProductID
trelOrderDetails Primary Key OrderDetailsID
OrderDetails OrderID
ProductID
The first uses a composite primary key; the second, a single primary key
and a unique index consisting of the two foreign keys. Referential
integrity is enforced in both cases.
Are there any non-trivial differences between these two schema? Both seem
fairly common. Are there reasons to prefer one to the other?
|
|
|
View Cart
