UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Supertype/Subtype    
 
   
rmter
post Nov 15 2008, 02:15 PM
Post #1

New Member
Posts: 6



If I make a "User" table with PK User_ID and want to make subtypes "Buyer" and "Seller", how do I make Access recognize the super/subtypes? I created a relation between User_ID and Buyer/Seller_ID with a renaming of the same ID, but in the relationship diagram it looks like a 1:1 relationship.
Go to the top of the page
 
+
jwhite
post Nov 15 2008, 02:26 PM
Post #2

UtterAccess VIP
Posts: 5,243
From: North Carolina, USA



Welcome to UA! -o!
Not clear on your description, but try something like:
tblUsers
---------------------------------
UserID, AutoNumber PK
UserTypeID, Number (FK)
LoginName, Text
LoginPassword, Text
NameFirst, Text
NameLast, Text
....
tblUserTypes
---------------------------------
UserTypeID, AutoNumber PK
UserType, Text
Go to the top of the page
 
+
rmter
post Nov 15 2008, 02:57 PM
Post #3

New Member
Posts: 6



Thank you very much for your response. I'm trying to make an ER diagram for a class in school which I actually have to do on a terrible piece of software, but it's going to be a lot easier for us if we do it on Access first and then copy our relationship diagram over to the other software.
In Access when you click on "Database Tools" and view the relationship diagram, how would you get it to show supertype/subtype between the user and its subs? How I have it now it's one:one and I need it to be 0:1. I'll attach the database so you can see what I'm talking about. There's a "unrelated objects" relationship report at the bottom of the All Tables column.
Attached File(s)
Attached File  3234DB.jpg ( 58.04K ) Number of downloads: 20
 
Go to the top of the page
 
+
rmter
post Nov 15 2008, 03:13 PM
Post #4

New Member
Posts: 6



Sorry I couldn't attach the database so I posted a screenshot.
Go to the top of the page
 
+
jwhite
post Nov 15 2008, 03:32 PM
Post #5

UtterAccess VIP
Posts: 5,243
From: North Carolina, USA



What you refer to as "supertype/subtype" is Primary Key and Foreign Key Relationship
Yyou refer to as "supertype/subtype" is Primary Key and Foreign Key Relationship
Your structure neds re-worked:
Table: User
----------------------------------
User_ID, AutoNumber, PK
User_Name
User_Email
User_Phone
Table: Sellers
----------------------------------
SellerID, AutoNumber, PK
User_ID, Number, FK
...seller details / options / etc.
Table: Bidders
---------------------------------
BidderID, AutoNumber, PK
User_ID, Number, FK
...buyer details / options / etc.
Table: Auctions
---------------------------------
Auction_ID, AutoNumber, PK
SellerID, Number, FK
....auction details
Table: Auction_Bidders
---------------------------------
AuctionBidderID, AutoNumber, PK
BidderID, Number, FK
...bidding details
In that this is for a class project/assignment, we shouldn't do it for you because you won't actually learn anything. But the tips above should get you back on the right track to get the rest done.
Go to the top of the page
 
+
rmter
post Nov 15 2008, 03:49 PM
Post #6

New Member
Posts: 6



Thanks again. I want the "buyer" and "seller" subtypes to inherit certain properties of "user", mainly "user_Id" as the primary key (renamed only) and the only differing properties would be the buyer/seller specific ones, thus creating a subtype inheriting properties of the super. All "user" types will contain generic information such as "User_Name" etc. that will be passed on to the subtypes. From a relational perspective, not all users will be buyers/sellers, but all buyers/sellers are users.
If Seller and Buyer are subtypes of User, they won't need a FK, correct? They are only required to have attributes that discern them from the parent type. Also, if they are subtypes of User and User is autonumber, I can't have the subs as autonumber also or the UserId will be different for each. I want that to remain the same. According to business rules, subtypes have the same primary key as their supertypes anyways, only differing attributes.
Is there any way to show a supertype/subtype in Access other than how I've diagrammed it in the previous attachment?
Go to the top of the page
 
+
jwhite
post Nov 15 2008, 04:36 PM
Post #7

UtterAccess VIP
Posts: 5,243
From: North Carolina, USA



Every table has (1) Primary Key, and may have multiple Foreign Keys to related information in other tables. Therefore, you HAVE to have the PK from [Users] as a FK in [Sellers] and [Bidders] tables. They are tables that further define what type of User they are.

What we are dealing with here is a long standing debate of how to relate tables to other tables. Some say Apples and some say Oranges. Sorry, but I have no interest in debating this. You can search here for many FAQs and threads about Normalization.

If you still have doubts, I would suggest you debate the issue with your Instructor/Professor.
Go to the top of the page
 
+
rmter
post Nov 15 2008, 04:51 PM
Post #8

New Member
Posts: 6



Thanks again for your time and help. My professor recommended breaking user into subtypes.....I was just wondering how to get the relationship diagram to show this in Access. Cheers!
Go to the top of the page
 
+
jwhite
post Nov 15 2008, 04:55 PM
Post #9

UtterAccess VIP
Posts: 5,243
From: North Carolina, USA



You're Welcome! thumbup.gif Good luck with your project!
Go to the top of the page
 
+
ace
post Nov 15 2008, 11:24 PM
Post #10

UtterAccess VIP
Posts: 5,415
From: Upstate NY, USA



It is done exactly the way you have it. In the Users table the UserID
is an Autonumber. In the sub type tables the UserID is a Long Integer.
The UserID is the primary key in both tables.
Referential integrity should be enforced in the sub type tables.
Go to the top of the page
 
+
rmter
post Nov 16 2008, 04:46 PM
Post #11

New Member
Posts: 6



Thanks Ace!
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 25th October 2014 - 07:18 PM