UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

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



Posts: 6
Joined: 15-November 08



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,483
Joined: 31-August 06
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



Posts: 6
Joined: 15-November 08



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: 25
 
Go to the top of the page
 
rmter
post Nov 15 2008, 03:13 PM
Post#4



Posts: 6
Joined: 15-November 08



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,483
Joined: 31-August 06
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



Posts: 6
Joined: 15-November 08



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,483
Joined: 31-August 06
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



Posts: 6
Joined: 15-November 08



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,483
Joined: 31-August 06
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,430
Joined: 26-November 05
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



Posts: 6
Joined: 15-November 08



Thanks Ace!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    29th August 2015 - 02:17 AM