Newbie with database design question one to many

[djmcats]

I am working on creating a database and as a newbie am not sure if I need to break up the data I am tracking in separate tables. This project was inherited, and I am questioning the design.

I am licensing an entity with one license number. Under that one license number includes the licensee, it's related business entities and key persons (one to many). All the data fields that I am tracking need to be tracked at the licensee, business entity and key person levels. There are just a few fields that would not be common to all. So is there any reason why I would not just create one long table? This table would not really have a unique primary key, and I'm not sure if it needs one?

A practical application example would be that I would have a Supervisor query the database for a license # and assign each record (business entity, person, etc.) a received date and a review analyst. One reason why we have to apply a date to each line item vs. an overall license number, is that at any given time a key person/entity can change and apply under that one license #. (Also see other tracking planned in the next paragraph.) The person who had the project before me had initially shown this Supervisor Assignment as a separate table. I don't see this as meaningful unless you reenter the licensee/persons/entity names (something unique to identify a relationship to each date and analyst assigned). The person who modeled the project was showing license # as a primary key, but it is not unique. The license # would be repeated for each business entity/person.

Another plan for the use of the database is to create a review tracking of steps performed when reviewing the licensee/business entity/key person: Tax Return A, IRS transcript A - etc. Either a yes/no box or date reviewed for each person/entity. This again in the orginial design was it's own separate table. Again, I don't see this as meaningful unless you reenter the licensee/persons/entity names (something unique to identify a relationship to each check box).

Why not create one large table and just limit the data needed for various users input and review purposes in the form of data entry forms and reports?

Thanks for your help.

[June7]

Don't understand. How can a license be associated with multiple businesses? Regardless, if that is the case then I expect need a master table of license numbers. Then related 'child' tables have the business info. Will each business have only one license? Then key persons are related to the business?

Analysts are assigned to business or to license?

[djmcats]

Don't understand. How can a license be associated with multiple businesses? Regardless, if that is the case then I expect need a master table of license numbers. Then related 'child' tables have the business info. Will each business have only one license? Then key persons are related to the business?

Analysts are assigned to business or to license?

One entity holds the license. Since that entity may be a child of a parent company (or several entity or individual owners that individually have 10% or more), the parent company/owners/entities must also be reviewed/vetted as part of the application for a license (but not assigned a license #). Key persons are reviewed because of their significant influence over the licensee - individual owners or officers of the company (but not assigned an individual license #). I have one simple table that assigns a license number to the licensee. Analysts are assigned to a license # or licensee and would be reviewing (tracking as noted above) each of their steps in the review process by entity and/or key person in the database. (The review process involves lots of paperwork and waiting - which is why management wants to track it.)

Normally upon renewal or new application the licensee 'case' including entities/key people are reviewed at the same time. In this case assigning an analyst would be a quick query of all line items associated with a license (entities and persons) and updating an analyst field. However, there would be times at anytime during the year where the President of a company leaves and we get an application for their replacement or entities are restructured/sold etc.. and we get new applications for business entities associated with a license. In these cases we would only be doing data entry and tracking for that one entity or individual at that time. Assignment is typically the same person who originally reviewed the license, but sometimes not depending on work load.

The main data table has contact information for each entity and person as well as some data related to business entity type (LLC, Corporation, Sole), Relationship to Licensee (owner individual, institutional investor, etc..)

So originally the project had us creating a table for assignment, a table for initial review, a table for supervisors review, management review, etc... All the steps that different people perform to assess productivity and progress. I am wondering if we need to track all of this at the same level of detail why not just one long table vs. additional tables? If using other tables, then I think I would need some arbitrary autonumbered primary key field in my main data table to make each record unique and use this field in each additional table.

Thanks.

[June7]

Possibly one table for the review tracking, if the review steps are well-defined and not expected to change in the long-term. If there is only one review track for each license, could be in the master license table. If each license # will have multiple review tracks and you want to retain all of them, then need a related child table.

Really need to clearly define data entities and how they relate. The above is an example of kinds of determinations that need to be addressed. This does sound like a complicated structure. Do a best effort at designing an ERD and post it for analysis if you still need guidance.

Yes, arbitrary autonumber unique ID might be useful.

[djmcats]

Possibly one table for the review tracking, if the review steps are well-defined and not expected to change in the long-term. If there is only one review track for each license, could be in the master license table. If each license # will have multiple review tracks and you want to retain all of them, then need a related child table.

Really need to clearly define data entities and how they relate. The above is an example of kinds of determinations that need to be addressed. This does sound like a complicated structure. Do a best effort at designing an ERD and post it for analysis if you still need guidance.

Yes, arbitrary autonumber unique ID might be useful.

Think of the licensee as the parent and its related business entities and key people as children. The tracking process steps are well defined and not expected to change. They are also the same for each record. For example, 2012 tax return review (applies to the licensee, its parent companies or business entity owners and its key people ). Are there any drawbacks to one long data table? My thoughts were that I could create process forms (ie. Supervisor Assignment, Analyst Review, Supervisor Review) to query a license # and have the form show the related detail records (parent and children) and the specific fields that need to be populated for that process.

[June7]

What do you mean by 'one long data table'? You want multiple fields for all the key people? Multiple similar fields is a problem. What if you wanted to find records associated with Key Person A - they could be located in any of the key people fields. And how many key people fields are needed - 2, 5, 10? This would not be a relational database. Using Access might make building data entry forms and reports easy but does not facilitate filtering and sorting. Might as well be a spreadsheet for all the good it does in that respect.

[Dal Jeanis]

I think you've made some conceptual errors in your thinking about the project. I'm assuming we're dealing with a liquor license or gambling establishment license or adult entertainment venue license, based upon the kind of reviews you are talking about.

Clearly, a license number is unique. The same license is just being associated with multiple different entities, one-to-many. Now, I would not use a license number that you get from an outside entity (such as the state of Nevada, or Microsoft) as the primary key, but a single license is a single entity, and it will generally be unique.

On the other hand, you're clearly correct that if you have five different kinds of reviews, there is no reason to have a separate table for each kind of review. Just have a review table which tracks all five types, with flags to show what kind they are.

Go over to Roger's Access Library at http://rogersaccesslibrary.com/ and review his tutorials on database design and application design. It explains a really basic technique for figuring out what "entities" your database is really representing. Start there. Nail down your entities and your business process, then come back with specific questions.

[djmcats]

I think you've made some conceptual errors in your thinking about the project. I'm assuming we're dealing with a liquor license or gambling establishment license or adult entertainment venue license, based upon the kind of reviews you are talking about.

Clearly, a license number is unique. The same license is just being associated with multiple different entities, one-to-many. Now, I would not use a license number that you get from an outside entity (such as the state of Nevada, or Microsoft) as the primary key, but a single license is a single entity, and it will generally be unique.

On the other hand, you're clearly correct that if you have five different kinds of reviews, there is no reason to have a separate table for each kind of review. Just have a review table which tracks all five types, with flags to show what kind they are.

Go over to Roger's Access Library at http://rogersaccesslibrary.com/ and review his tutorials on database design and application design. It explains a really basic technique for figuring out what "entities" your database is really representing. Start there. Nail down your entities and your business process, then come back with specific questions.

One question - Why would you create an ID number to replace a simple data fact like type of entity? For example, Entity Type would be: Corporation, LLC, Partnership, Trust This could be a simple drop down box for data entry to deter spelling errors. This way the entry would not require someone to know that 100 is Corporation, etc... Thanks.

[June7]

Dal suggested a lookup with alias. This means the ID is saved to table but user sees the descriptor. This is accomplished with a multi-column combobox or listbox. http://datapigtechnologies.com/flash...combobox3.html

Saving the ID or descriptor is a determination you will have to make. There are advantages/disadvantages for each.

[Dal Jeanis]

djmcats - For something simple, you wouldn't have to. There's nothing wrong with using "Corporation" as the field value, rather than having a key field and a name field. On the other hand, if there were a lot of differences between corps and partnerships, you might create a key-driven file to hold information about some of those differences. It's a craft, not a science.