Okay. I've done my homework, researched the company model, researched
Access, and have written up my first skeletal relationship diagram
that I believe is NF3. Here is a link: http://johnharris34.googlepages.com/database
I'm essentially wondering if there's a problem with how it's set up
because it does seem slightly strange compared to other diagrams I've
looked at, but I'm not sure how to represent my data model any more
accurately. Explanation:
(StockNumber is an internally (by the office manager) generated number
that is always unique.)
[Products] represents all the "extras" we sell, like extended
warranties, leather protection, hitch, etc. (There are about 10
products total that we sell.) We usually sell a car with extras
thrown in as part of the sale, sometimes there are no extras
purchased. And sometimes we sell extras by themselves, without any
car sale (in those instances [ProductSales].[AutoStockNumber] will be
NULL.) There is *actually* a one-to-one relationship between
AutoSales and Sales, because we never sell more than one car as part
of one sale; however as you can see in the diagram I've set it up as
one-to-many.
I appreciate any feedback I can get on this mock-up. 13  1194 
JohnH wrote:
Okay. I've done my homework, researched the company model, researched
Access, and have written up my first skeletal relationship diagram
that I believe is NF3. Here is a link:
http://johnharris34.googlepages.com/database
I'm essentially wondering if there's a problem with how it's set up
because it does seem slightly strange compared to other diagrams I've
looked at, but I'm not sure how to represent my data model any more
accurately. Explanation:
(StockNumber is an internally (by the office manager) generated number
that is always unique.)
[Products] represents all the "extras" we sell, like extended
warranties, leather protection, hitch, etc. (There are about 10
products total that we sell.) We usually sell a car with extras
thrown in as part of the sale, sometimes there are no extras
purchased. And sometimes we sell extras by themselves, without any
car sale (in those instances [ProductSales].[AutoStockNumber] will be
NULL.) There is *actually* a one-to-one relationship between
AutoSales and Sales, because we never sell more than one car as part
of one sale; however as you can see in the diagram I've set it up as
one-to-many.
A few quick thoughts.
Seems to me that Vehicle is an entity of it's own. Vehicles exist
without a sale.
Not sure what I would do with the trade in info. I suppose it is
possible that more than one vehicle could be taken in trade on a
single sale. If so trade in info belongs in another table.
How will you determine which other table the SaleId in the Sales
table refers to?
On Mar 6, 11:20 am, rkc <r...@rkcny.yabba.dabba.do.comwrote:
JohnH wrote:
Okay. I've done my homework, researched the company model, researched
Access, and have written up my first skeletal relationship diagram
that I believe is NF3. Here is a link:
http://johnharris34.googlepages.com/database
I'm essentially wondering if there's a problem with how it's set up
because it does seem slightly strange compared to other diagrams I've
looked at, but I'm not sure how to represent my data model any more
accurately. Explanation:
(StockNumber is an internally (by the office manager) generated number
that is always unique.)
[Products] represents all the "extras" we sell, like extended
warranties, leather protection, hitch, etc. (There are about 10
products total that we sell.) We usually sell a car with extras
thrown in as part of the sale, sometimes there are no extras
purchased. And sometimes we sell extras by themselves, without any
car sale (in those instances [ProductSales].[AutoStockNumber] will be
NULL.) There is *actually* a one-to-one relationship between
AutoSales and Sales, because we never sell more than one car as part
of one sale; however as you can see in the diagram I've set it up as
one-to-many.
A few quick thoughts.
Seems to me that Vehicle is an entity of it's own. Vehicles exist
without a sale.
Not sure what I would do with the trade in info. I suppose it is
possible that more than one vehicle could be taken in trade on a
single sale. If so trade in info belongs in another table.
How will you determine which other table the SaleId in the Sales
table refers to?- Hide quoted text -
- Show quoted text -
Seems to me that Vehicle is an entity of it's own. Vehicles exist
without a sale.
Yes, that is true, and I suppose I could split the table on the chance
that our business model changes. Currently, however, there is never a
need to track vehicles independently of sales because as a brokerage,
we only facilitate transactions of cars; we don't have a formal
inventory as such.
Not sure what I would do with the trade in info. I suppose it is
possible that more than one vehicle could be taken in trade on a
single sale. If so trade in info belongs in another table.
I've been told there is never a situation where more than one trade-in
is taken in on a vehicle, although I've planned to split it out anyway
(and should have before putting that diagram up), since there's no
point painting myself into a corner.
How will you determine which other table the SaleId in the Sales
table refers to?
I'd say that's a good question since I'm not sure how to answer it :D
I guess you mean how will I know what the Sale was for?
Isn't it possible to join the three tables (Sales, AutoSales,
ProductSales) to retrieve whatever information I need? I haven't
started writing queries against the tables yet, and my limited
experience doesn't throw much light ahead of me to see by. Perhaps it
would be a good idea to put a boolean field in Sales to indicate
whether a vehicle was purchashed as part of the Sale? What do you see
as the potential problem?
>Currently, however, there is never a
>need to track vehicles independently of sales because as a brokerage,
we only facilitate transactions of cars; we don't have a formal
inventory as such.
I guess I don't really know what you're modeling then.
What exactly does a brokerage do?
On Mar 6, 11:59 am, rkc <r...@rkcny.yabba.dabba.do.comwrote:
>Currently, however, there is never a
>need to track vehicles independently of sales because as a brokerage,
>we only facilitate transactions of cars; we don't have a formal
>inventory as such.
I guess I don't really know what you're modeling then.
What exactly does a brokerage do?
A auto brokerage is a licensed auto dealer who serves as a middleman
between customers and dealerships (buyers and sellers), essentially.
In case you're curious:
Someone who wants to buy a car submits a vehicle request (2007 Toyota
Camry, leather, red, etc) to the brokerage. A sales guy finds the car
for them, usually at dealerships, on autotrader, etc. The brokerage
can genearlly negotiate much better pricing than the customer can, so
he "passes on the savings". The brokerage makes money and the
customer avoids all the hassles of dealing with high pressure salesman
with inventories to push. Really a nice way to buy a car.
So the only inventory we ever have is from the trade-ins we take in,
and those are piecemeal. We really only formally keep a record of them
when they're sold. Until then they exist only in a filing cabinet,
and it would really only add unnecessary complexity to set up an
inventory system just for their sake.
You didn't complete the initiation yet!
If I could, I'd make a few suggestions:
1) PartnerName I assume is the salesperson. I'd remove those fields,
put in a SalesPersonID and then create a seperate table for
salespersons, linked by their ID.
2) The Product table should also include ProductID, and be linked by
that field.
3) I think the concept of a sale should include an "outbound" item
(new car) and an "inbound" item (trade-in). As such, I think
seperating the two pieces into 2 seperate tables, linked by SaleID,
might be a better structure.
4) I don't think the relationship between AutoSales and ProductSales
is necessary. In fact, I think it's going to cause some referrential
integrity issues.
5) Make sure VIN in AutoSales and ProductID in ProductSales are both
indexed as Key fields, since they must be unique.
*gives JohnH a wedgie*
Welcome to the club.
On Mar 6, 1:22 pm, "JohnH" <JohnHarri...@gmail.comwrote:
>
I appreciate any feedback I can get on this mock-up.
On Mar 6, 1:51 pm, "ManningFan" <manning...@gmail.comwrote:
You didn't complete the initiation yet!
If I could, I'd make a few suggestions:
1) PartnerName I assume is the salesperson. I'd remove those fields,
put in a SalesPersonID and then create a seperate table for
salespersons, linked by their ID.
2) The Product table should also include ProductID, and be linked by
that field.
3) I think the concept of a sale should include an "outbound" item
(new car) and an "inbound" item (trade-in). As such, I think
seperating the two pieces into 2 seperate tables, linked by SaleID,
might be a better structure.
4) I don't think the relationship between AutoSales and ProductSales
is necessary. In fact, I think it's going to cause some referrential
integrity issues.
5) Make sure VIN in AutoSales and ProductID in ProductSales are both
indexed as Key fields, since they must be unique.
*gives JohnH a wedgie*
Welcome to the club.
On Mar 6, 1:22 pm, "JohnH" <JohnHarri...@gmail.comwrote:
I appreciate any feedback I can get on this mock-up.- Hide quoted text -
- Show quoted text -
First of all, very good points, thank you. I have comments about all
of them.
1) PartnerName I assume is the salesperson. I'd remove those fields,
put in a SalesPersonID and then create a seperate table for
salespersons, linked by their ID.
PartnerName is actually used to keep track of spouses or business
deals where a joint title is issued. Perhaps subconsciously I've been
trying to avoid complexity, since thinking about it now I realize
there is a possibility that the two parties in a joint title could
have different addresses and phone numbers. I guess I'll have to
think about that and decide if it's going to be necessary for the
purposes intended (probably not).
2) The Product table should also include ProductID, and be linked by
that field.
Why is that? Products is essentially a lookup table, and two products
will never have the same name. Is it a performance issue?
3) I think the concept of a sale should include an "outbound" item
(new car) and an "inbound" item (trade-in). As such, I think
seperating the two pieces into 2 seperate tables, linked by SaleID,
might be a better structure.
I understand what you're saying, but that conflicts with the current
model entirely, since a Sale doesn't necessarily include a vehicle
purchase at all (we might sell window tinting by-itself).
4) I don't think the relationship between AutoSales and ProductSales
is necessary. In fact, I think it's going to cause some referrential
integrity issues.
I had wondered about that and that's one of the specific aspects to
the prelim design I was hoping would be clarified. I haven't delved
into the joins that are going to be involved, but I did question
whether the extra relationship might be superfluous as well, since
they're all linked by SaleID.
5) Make sure VIN in AutoSales and ProductID in ProductSales are both
indexed as Key fields, since they must be unique.
Will do.
Here is the current revised relationship diagram: http://johnharris34.googlepages.com/Relationships.JPG
Is there a secret handshake? Goodie bag?
Followups:
" I understand what you're saying, but that conflicts with the current
model entirely, since a Sale doesn't necessarily include a vehicle
purchase at all (we might sell window tinting by-itself)."
In this case, the "outbound" item is a window tinting. There would be
no "inbound" item associated with it. I gues it's "6 of one, half-
dozen of the other" but I think it simplifies the table layout.
"Is there a secret handshake? Goodie bag?"
There's a "teabag", but you should be glad you didn't get it.
JohnH wrote:
>>4) I don't think the relationship between AutoSales and ProductSales
is necessary. In fact, I think it's going to cause some referrential
integrity issues.
I had wondered about that and that's one of the specific aspects to
the prelim design I was hoping would be clarified. I haven't delved
into the joins that are going to be involved, but I did question
whether the extra relationship might be superfluous as well, since
they're all linked by SaleID.
Is there a secret handshake? Goodie bag?
No. Nothing but advice, good, bad and mediocre. Topped with
occasional abuse.
If you are thinking of Sales as you would an Order that can
be made up of one or more Items (ProductSales or AutoSales) then
Cost and SalePrice are probably misplaced. They would be
in the Autosales and ProductSales tables. Totals would be calculated
when needed.
VehicleStockNumber in the ProductSales table is redundant. It is already
in the AutoSales table. If a product and Auto are included in the
same sale it can be determined by the AutoSales record related to the Sale.
There's a "teabag", but you should be glad you didn't get it.
pretty much, I don't even like tea.
As far as the inbound/outbound idea.. I guess I'm not following you.
It seems like you're suggesting that I just change the TradeIns
relationship to be between Customers and TradeIns, rather than
AutoSales and TradeIns. (TradeIns is the only possible inbound
transaction and outbound transactions would still need to be split
apart into AutoSales and ProductSales.) Does that make sense or am I
misunderstanding you?
rkc:
Yeah that's vestigial to an earlier draft that I'd forgotten to fix.
It's really quite funny of me ask for suggestions on diagrams with
stupid errors in them. Sorry bout that.
And your second suggestion is helpful. I've fixed that now.
On Mar 6, 7:38 pm, "JohnH" <JohnHarri...@gmail.comwrote:
pretty much, I don't even like tea.
Oh, believe me. There's not really "tea" in the teabag...
Your picture is looking prettier, but you still have a Products table
with no Product ID. I'd use the ID because it's easier to not have to
worry about double-quotes when defining "like" terms. Besides, every
other table uses IDs, so why not Products?
I would use your trade-in's VIN as their ID. Don't know if you
already made that decision or not.
If you can, ALWAYS make your relational fields the same name. There's
nothing more frustrating then coming in the morning after your lead
coder gets gunned down outside a strip club and trying to figure out
if PersonID in one table is the same as EmployeeIN in another. Just
name them both PersonID and it's self-explanatory. It looks like
there are 2 ID fields that you need to change to comply with this, I'd
go ahead and do it unless you have a REALLY good reason not to.
Other than that, it looks like you're in good shape. Of course,
theory and practice are sometimes vastly different and once you start
populating it with test data you might need to make a few adjustments.
On Mar 7, 12:31 pm, "ManningFan" <manning...@gmail.comwrote:
On Mar 6, 7:38 pm, "JohnH" <JohnHarri...@gmail.comwrote:
pretty much, I don't even like tea.
Oh, believe me. There's not really "tea" in the teabag...
Your picture is looking prettier, but you still have a Products table
with no Product ID. I'd use the ID because it's easier to not have to
worry about double-quotes when defining "like" terms. Besides, every
other table uses IDs, so why not Products?
I would use your trade-in's VIN as their ID. Don't know if you
already made that decision or not.
If you can, ALWAYS make your relational fields the same name. There's
nothing more frustrating then coming in the morning after your lead
coder gets gunned down outside a strip club and trying to figure out
if PersonID in one table is the same as EmployeeIN in another. Just
name them both PersonID and it's self-explanatory. It looks like
there are 2 ID fields that you need to change to comply with this, I'd
go ahead and do it unless you have a REALLY good reason not to.
Other than that, it looks like you're in good shape. Of course,
theory and practice are sometimes vastly different and once you start
populating it with test data you might need to make a few adjustments.
All your suggestions are very helpful. I'm going to hold off changing
the ID for tradeins to VIN since I'm still waiting to see if
management will even okay recording those VINS, since the dataentry
for a 17 digit alphanumeric is going to be tedious and errorprone.
Thanks for all the help. I've started scrubbing and I'm writing an
import routine with all kinds of fun queries to squeeze the old data
into its new dress and it's going quite well, surprisingly. I didn't
think I had chops with SQL, but now I'm feeling some vague urge to
learn electric guitar or drums or something.
And actually while writing this I've been working on something and
realize now that I have a toothy problem that my first impulse
solution is not gonna work for. Maybe you might get a thrill helping
me out with it. Here it is:
Most of our referrals and therefore most of the lien holders
(financers) on these cars are Credit Unions. We work closely with the
credit unions on all sorts of things, and I have a table that is
called, appropriately, CreditUnions. But there are other ways
customers can get referred, and there are other financial institutions
that can finance cars. Outline of how I handled each one (referrals
and lien holders):
1. Referral Source:
I created a union query called "ReferralSourcesAll" that combines
CreditUnions with another table called ReferralSources. (I set up the
PKID in ReferralSources to start at 10000 so it would never conflict
with CreditUnionID {although it's very clever how Access recognizes
the union query and steps autonumbers automatically anyway})
2. Financers:
I created a union query called "FinancersAll" that combines
CreditUnions with another table called OtherFinancers. Same
situation, basically.
The reason I've done what I've done, naturally, is because I don't
want a bunch of duplicate data in tables and I want CreditUnions to
stay in one piece, alone, whole, clean, pure, like a white porcelein
doll sitting on the lap of an angel.
The problem with what I've done with union queries is that I can't
enforce any kind of referential integrity (from what I know). At
first I didn't think this was going to be a problem, but then I woke
up in a puddle of my own drool and realized that Credit Unions merge,
change names, etc.
WWYD?
The diagram: http://johnharris34.googlepages.com/Relationships.JPG
On Mar 7, 6:42 pm, "JohnH" <JohnHarri...@gmail.comwrote:
>
The problem with what I've done with union queries is that I can't
enforce any kind of referential integrity (from what I know). At
first I didn't think this was going to be a problem, but then I woke
up in a puddle of my own drool and realized that Credit Unions merge,
change names, etc.
WWYD?
Guh. Hmmm... Well, first wipe the drool off your chin. After that,
I think you're still O.K.. If a new credit union emerges, just create
a new ID since technically it's a new identity. If 2 credit unions
merge and still use one of the credit union's names, just use that
union's ID because technically it's the same entity. Don't overly
complicate it, this should allow you to avoid alot of hassles.
On Mar 8, 5:45 am, "ManningFan" <manning...@gmail.comwrote:
On Mar 7, 6:42 pm, "JohnH" <JohnHarri...@gmail.comwrote:
The problem with what I've done with union queries is that I can't
enforce any kind of referential integrity (from what I know). At
first I didn't think this was going to be a problem, but then I woke
up in a puddle of my own drool and realized that Credit Unions merge,
change names, etc.
WWYD?
Guh. Hmmm... Well, first wipe the drool off your chin. After that,
I think you're still O.K.. If a new credit union emerges, just create
a new ID since technically it's a new identity. If 2 credit unions
merge and still use one of the credit union's names, just use that
union's ID because technically it's the same entity. Don't overly
complicate it, this should allow you to avoid alot of hassles.
That's pops-a-valium-reasonable. Thanks again. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ken |
last post by:
Hi. This is a followup to my NULL macro vs. 0 question. It's in a
separate thread, because I need to post using google, so my original
thread isn't available to me yet :(.
Anyway, I just...
|
by: Don Vaillancourt |
last post by:
Hello all,
Over the years as I design more database schemas the more I come up with
patterns in database design. The more patterns I recognize the more I
want to try to design some kind of...
|
by: sk |
last post by:
I have an applicaton in which I collect data for different parameters
for a set of devices. The data are entered into a single table, each
set of name, value pairs time-stamped and associated with...
|
by: MAFDoit |
last post by:
NEWSGROUP:
This is a followup to the post below. The original post was helpful and I
now know many ways to RETRIEVE information from Windows explorer, but I
haven't yet found a way to INSERT or...
|
by: rodchar |
last post by:
Hey all,
I'm trying to understand Master/Detail concepts in VB.NET. If I do a data
adapter fill for both customer and orders from Northwind where should that
dataset live?
What client is...
|
by: tshad |
last post by:
Many (if not most) have said that code-behind is best if working in teams -
which does seem logical.
How do you deal with the flow of the work?
I have someone who is good at designing, but...
|
by: Earl |
last post by:
Is it poor practice to have one custom library depend on another custom
library? For example, my data manipulation library depends on my replication
library, which in turn pulls serial data out of...
|
by: neelsmail |
last post by:
Hi,
I have been working on C++ for some time now, and I think I have a
flair for design (which just might be only my imagination over-
stretched.. :) ). So, I tried to find a design...
|
by: indrawati.yahya |
last post by:
In a recent job interview, the interviewer asked me how I'd design
classes for the following problem: let's consider a hypothetical
firewall, which filters network packets by either IP address,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
