Draw Relationship diagram with supertype-subtype entities (1 Viewer)

AccessPractice

Registered User.
Local time
Today, 13:40
Joined
Jun 24, 2016
Messages
25
Hi

I cannot draw the supertype-subtype entities.
I want to have Accessories' table as a supertype and Tapes' and Buttons and more(in the future) as subtypes.
When I drew it, it showed error as in the attached file.

Please help
Thank you
 

Attachments

  • Relationship.jpg
    Relationship.jpg
    96.9 KB · Views: 367

Minty

AWF VIP
Local time
Today, 06:40
Joined
Jul 26, 2013
Messages
9,082
You shouldn't have circular relationships, and you shouldn't call your fields simply ID . Also the accessories table seems meaningless - it has no data, other than the ID.

As soon as you start calling Tables by an actual data item you are not normalising correctly eg, tblButtons tblTapes. These should be stored in one table as Items. Each Item type can have its own properties stored in the items table.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:40
Joined
Jan 23, 2006
Messages
14,015
AccessPractice,

Can you tell us more about the situation you are trying to model? As Minty suggested it is a good practice to name your id fields with the name (or some part) of the entity.
eg: CustomerID, OrderID
It gets confusing in some models and databases where the identifier in every entity /table is just ID. You will also find that many Access user/developers do not use names with embedded spaces. This reduces some syntax errors and frustration.

In Supertype/subtype, the key of the supertype is included in each subtype. In fact the common attributes are left in the supertype and only the specialized attributes (that differ from the supertype) are stored in the each subtype.

There are not many thorough examples of super/subtype relationships carried through from logical to physical implementation (at least not that I have found).

Good luck with your project.
 

AccessPractice

Registered User.
Local time
Today, 13:40
Joined
Jun 24, 2016
Messages
25
Hello guys!
Thank you for your replies.
The reason I think I have to use supertype-subtype is that Tapes and Buttons don't have the same number of attributes and don't have the same units.

For example, Tapes has Color, Type, YarnComposition, Size, etc.
whereas Buttons also has Color, Type(not the same kinds of Tape's type), Size (not the same as well), but doesn't have YarnComposition.

About units, Tapes are counted by length (meters/Yards) but Buttons are counted by piece.
and there are more than two accessories in my stocks' database.
 

Minty

AWF VIP
Local time
Today, 06:40
Joined
Jul 26, 2013
Messages
9,082
The way to model this is known as an EAV - have a read here https://en.wikipedia.org/wiki/Entity–attribute–value_model
Multiple tables (and it sounds like a lot) will cause you a whole world of pain later on in the development.

Every new item that comes along will require a complete re-write of huge chunks of your code and forms if you do it the way you are trying to at the moment.
 

AccessPractice

Registered User.
Local time
Today, 13:40
Joined
Jun 24, 2016
Messages
25
Hi Minty
I have edited the relationship using your advice.
It is in the attached file.
However, I have a problem that in the attributes table,
in the attributeValue's field, there are many data types in it.
For example :
Color-->ShortText
Picture-->Attachment
Size-->Number (I may use ShortText i.e. 8 cm, 10.5 inches)
So I think I should use Variant data type, but there is no Variant type in the interface.
How can I solve the problem? (I think about using VBA as well but not sure how.)

Another problem is that there are a lot of records in my accessories (OnlyTape has more than 1,000 records!) which are stored in Excel's worksheets.
So if I add records in Access's tables, there are no simple ways to do it for example,
In Excel:
Tape has ID, Color, Type etc. columns
But in Access these columns are the AttributeName field values
and the Excel columns' values are the AttributeValue field values.
 

Attachments

  • RelationshipEdited.png
    RelationshipEdited.png
    62 KB · Views: 291

jdraw

Super Moderator
Staff member
Local time
Today, 01:40
Joined
Jan 23, 2006
Messages
14,015
AccessPractice,

Do you have a statement of requirements or some material that describes the "business and business processes" involved? You have given a few points, but a more comprehensive set of requirements would be helpful to readers and to you as you move forward.

The article referenced by Minty has many good points and concepts and also some cautions. I suggest you create some test data (or select some real data) and work that data in scenarios against you evolving model. Reconcile every anomaly and test again. Repeat until it works as you need it.

See my article on stump the model

Good luck. Keep us posted of your progress.
 

AccessPractice

Registered User.
Local time
Today, 13:40
Joined
Jun 24, 2016
Messages
25
Hi jdraw

First of all I would like to tell my background, I work with my family business which is a garment factory. My mother who is the head of the company assigns a project to me

My project is to build an application which systematically collects stocks.
There are many stocks' records which are stored in the Excel files.

The descriptions are as follow:

1. There are a number of accessories(=stocks) e.g. Tapes, Buttons, Shirts.
2. These accessories usually don't have the same number of attributes.
3. Even though there are the same name of attribute, they are not the same kind
i.e. Type of tape <> Type of button
4. We want to collect the quantities of the accessories, however they may not in the same units i.e. Tape in meters/yards, Button by piece
5. We want to add (for example) new tape's record or the quantity of the available tape.
6. We want to subtract (for example) the quantity of tape.
7. (from 5.) the addition is done by purchasing them from suppliers
(So there are following Purchasing Orders and Purchasing Order Details' tables.)
8. (from 6.) the subtraction is done by using them as the composition of products (shirts, dresses, pants etc.) which customers want to buy. (So again there are following Orders and Orders Details' table.)
9. The suppliers who sell the accessories to us have their own code numbers for each accessory they have and we also have our codes for the accessories we've bought.
10. The supplier's accessories have their prices per unit which are different from time to time and maybe their isn't just one supplier who sells the accessory to us .
11. For the customers side, they buy our products (shirts, pants etc.).
the accessories are usually used for composition and decoration of the products.
12. The customers have their own code for the products they order (called style number) and ours have our own code which are different from them as well.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:40
Joined
Feb 28, 2001
Messages
21,585
For this kind of messy mixed-bag relationship, the rule is Divide and Conquer. Pick apart the problems of description from the problems of manipulation. That is, it is easy to track how much of something you purchase or consume. It is easy enough to describe variable lots of "things." But you need to keep those questions separate.

From your earlier posts, you very seriously need to study the concept of database normalization. You can web-search that easily. I'm betting that many college sites will have tutorials on the subject.

From post #8 in the thread, you will need to have tables for base accessories, descriptions, purchase orders, suppliers, customers, etc. You will probably need several of what we call "Junction" tables that help you translate your supplier numbers to your internal numbers and to translate your internal numbers to your customer style numbers. You might also have junction tables to give you "component lists" of how much tape, how many buttons, how much cloth, how much other thread you expect to consume to make one shirt or one pair of pants.

Though it might be possible to do this entirely through queries, forms, and reports - with minimal VBA - it might be a good idea to be ready to really get deep in the trenches. What you are describing is NOT a trivial problem, so don't expect it to come together right away.

From the discussion, I have to say that you aren't quite ready to do this yet because you need more problem decomposition analysis (as mentioned earlier). I.e. you have not divided it finely enough to conquer it yet. My simplified "Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.

Access is not a smart tool. There are times when it is dumber than a box of rocks. It has no idea of what goes into making pants. You have to supply the smarts and the know-how. That leads to the other "Old Programmer's Rule" - Access won't tell you anything you didn't tell it first. You have to be able to gather the data you need to produce the output you want - which means that part of the design phase is to work BACKWARDS. In essence, you say: I want to see a report that lists my consumables shown in a particular way. Then you say: What data must I have gathered in order to product that report? Then you say: How do I gather these items and store them in the database? For every output you need, be it form or report or raw text file, you need to go through that exercise.

I doubt any of us could write any significant parts of this as examples. You are talking about a huge project and make no mistakes in that perception. All we can probably do is (a) offer analysis guidelines and (b) if you have very narrowly-crafted questions about how something works, answer those questions for you.
 

AccessPractice

Registered User.
Local time
Today, 13:40
Joined
Jun 24, 2016
Messages
25
Dear The_Doc_Man

Do you have sample ER-diagrams or databases as a whole which are similar to my project? I know I am not ready or exactly competent to do it But I have read both database theory and how to use Access for about a year. Yes a year!
But just reading and practising exercises in books don't help me to be able to do the real project which is more complicated than them. And so forum friends suggested me that I have to really do it.
I know it is huge as you said but if I haven't start to do it yet when I will be ready or I will have to read and practice for another year.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:40
Joined
Jan 23, 2006
Messages
14,015
I agree with the Doc_Man -Access won't do anything you haven't told it and "... If you can't do it on paper then you can't do it in Access. .....
That's why I often suggest building a model and testing/reviewing it using pencil and paper to create a "blueprint".
Also, his comments re: Inventory is not trivial can not be over emphasized. Especially when you have multiple products with multiple, varying attributes.
Also, taking a complex problem and breaking it into "do-able chunks" is a good strategy.

You have a good start with your analysis, but you will require more detail as you move from logical to physical.
Another thing you could do is to focus on some aspect of the business. Start at high level concept and then move to detailed logical, and work it into a physical implementation, This, in reality, is experiencing the process of analysis---to -- physical in a step by step approach. Understand the process and use it for other areas of the business. By this means, you will learn a process that works for you, and that you can transfer to other areas of the business.
Be prepared to make some mistakes, but make them early and learn from them. Don't try to take on everything in detail at the same time.

Make a conceptual level diagram/model of your proposed "new, automated business" --but only at the statement/description level of detail. The point here is just to identify things that would/should exist. This serves as a "picture/artist concept" for reference. The idea here is to have a reference model that shows what and how generally the systems and sub (and sub-sub) systems fit.
The analogy to show what is being proposed here is building a shopping centre or apartment complex and focusing on the stairway railing without any concern for electrical, plumbing..basic infrastructure. etc. There is an order to the way development starts and proceeds and working from a "blueprint".

There was a poster (can't find a link at the moment) who had a similar situation --also garments for men,women, children; different sizes; colors; fabrics....
I provided the attached diagram/model for consideration ---concepts only, no details.

It may be of some use to you.

Good luck.
 

Attachments

  • ConceptualFinishedProductWithManyAttributes.jpg
    ConceptualFinishedProductWithManyAttributes.jpg
    52.5 KB · Views: 357

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:40
Joined
Feb 28, 2001
Messages
21,585
AccessPractice, I came up in an environment where ER diagrams weren't available to us. I'd say we were operating on a shoestring, but if so, it had the consistency of cooked angel-hair pasta. Having actual analysis tools? I wish...

So regrettably no, I have no examples to offer. But here is a point to consider. Almost ANY sample database that does inventory management will provide examples that can be translated to your environment as long as you don't take everything too literally. It isn't always about adapting code (though that never hurts...) but more about seeing how other folks approached a similar problem.
 

AccessPractice

Registered User.
Local time
Today, 13:40
Joined
Jun 24, 2016
Messages
25
Hi
I edited drawing the relationship of Items as in an attached file.
Is it correct to draw it like this?
 

Attachments

  • Relationship_edited.png
    Relationship_edited.png
    33.8 KB · Views: 173

jdraw

Super Moderator
Staff member
Local time
Today, 01:40
Joined
Jan 23, 2006
Messages
14,015
AccessPractice,

A model does or doesn't make sense when you compare it/test it with your business processes and rules. I'm not familiar enough with your business to comment, but you can use some sample data and get some "feel"/appreciation of how well the model supports your business.

Here is a free youtube video on sub/super type by Mark Serva. It may help with some details and concepts.

Good luck.
 

AccessPractice

Registered User.
Local time
Today, 13:40
Joined
Jun 24, 2016
Messages
25
Hi

I have come to the solution. I took an Access course last week.
The teacher told me to put all types of accessories in one table i.e. tblItems
and just query the specific type I want. There are some attributes that are gap blank (one that tape has but not button) but it does not matter.

Just want to tell you and thank you for all your advice.
 

jdraw

Super Moderator
Staff member
Local time
Today, 01:40
Joined
Jan 23, 2006
Messages
14,015
Glad you have a solution. It could be helpful to others, and make a good reference, if you could post a copy of your database. There are not many references for a business solution that requires super/sub type relationships and has the follow on physical implementation. Most articles/samples stop at the model level.

Happy to help.
 

Users who are viewing this thread

Top Bottom