Subtypes and Supertypes
The Annoyance : I’m creating a database getting a sanctuary cardiovascular system you to definitely servers of many different kinds of incidents-workshops, wedding receptions, and the like. I’m convinced that every particular skills demands its very own table, because the types of information we assemble for each and every experiences form of may vary. Manage I create a single desk that have everything in they, or multiple dining tables that i somehow connect with each other?
The new Fix : Workshops, wedding parties, and so on are typical subtypes of one supertype: occurrences. Because subtypes and you will supertypes are around the globe, they’ve been pretty common in the database as well. For example, teams will be subdivided to your relationship, short term, and you may excused. Every occurrences have some well-known characteristics (title, area, and the like), but different types of occurrences provides their own characteristics since the really (weddings keeps caterers, workshops provides coaches, and stuff like that). When faced with throwing this sort of advice, a database developer need choose from having fun with just one desk, having fun with numerous unrelated dining tables, otherwise carrying out an excellent supertype table (that has the common qualities) that’s related to numerous subtype tables (which includes the unique services).
The best option is just one table you to definitely accommodates the items. This works whether your research your collect is mainly a similar for all experiences products. Such as, in the event that all sorts regarding skills have a-start go out, an-end time, a sponsor, and the like, after that just go right ahead and create just one occurrences dining table which have an industry you to definitely determine the event style of. While the chatted about more than, you will most certainly need a few most industries to own qualities which might be particular to specific experiences types. Which is great; la loro spiegazione merely log off those people areas empty when they are not required.
However, in the event that event products are very different somewhat, on one desk cannot make sense. Wedding events and you may workshops, eg, might need a completely different number of areas, so you may just go ahead and would separate dining tables to possess them. This is simply not a detrimental service, it may cause issue later. Particularly, you’ll want to collect payments for variety of occurrences, so your repayments desk need to have a foreign trick occupation on ID of experiences-but how are you aware whether that ID refers to the wedding events table or the workshops dining table?
For almost all aim, you can clean out tables which might be during the a-one-to-you to relationships as if they are one table
The answer is with supertypes and you may subtypes in your structure, due to the fact found inside Figure 3-14. This provides the finest out-of both the single table and numerous desk choice.
Earliest perform an events desk, and construct brand new fields preferred to all the knowledge items. Next perform subtype tables, such wedding receptions, workshops, etc. Provide for every subtype an initial key that’s compatible with the fresh new priple, when your incidents table uses an AutoNumber ID industry, promote each subtype table an enthusiastic ID occupation that is a variety study sorts of, towards Industry Proportions set-to Enough time Integer. (The fresh new international trick community can keep people study that would be legitimate in the primary trick profession, and generally speaking, you will be making the brand new overseas key a comparable research kind of just like the top trick. It’s not visible, but AutoNumbers was A lot of time Integers.) Next, perform a relationship amongst the supertype each subtype of the hooking up an important tips of these two dining tables (one to supertype, that subtype) about Matchmaking screen. e., that each and every listing about supertype desk commonly match just one to number within the subtype tables.
Create them each other to a query, plus the sign-up to your first secret profession makes them behave same as an individual dining table. Furthermore, there’s no need to use a beneficial subform whenever together with each other tables to your a type. Merely feet the proper execution towards the an inquiry in which they have been joined-that is what the proper execution Wizard really does.