Beginner - Need help with direction I need to take with Database...

[ItsMe]

OK, I understand.



There are tables on a server. It is likely you will need to edit data on these tables and append records. You will need to understand how these tables relate to each other. This data is known as persistent data. You will need an ERD of the persistent data. You will need a way to connect to the tables that already exist. You will need to understand what the data types are within the existing database.

Perhaps one approach would be to break off one small piece of the big picture and work with the DBA regarding one small piece. In other words, plan on creating one small app that is an interface for a single entity that exists within the tables. Perhaps a candidate would be Departments. You could focus on creating an application that can Create Read Add and Delete departments. With that, you can ask about the schema of the Departments table. Understand the columns, constraints, Keys, etc. that comprise the Departments table.

You may discover that your Business Rules, which are part of the Application process, need to interact with entities that do not exist within the current database. In that case, you will need to create your own database or create additional tables in SAP (if creating tables in SAP is possible). Find an entity that exists within the current database and create a diagram based on that. Then, create an application to interact with the single entity.

[Homegrownandy]

Im homegrown mate.

[AdamLeeZ]

Thanks for your knowledge. Now off to work to start this...

[AdamLeeZ]

Another question. I'm writing out the ERD and concerned that the tables i'm building aren't structured right. Can I have an 'Employee' table that has their email and phone number? or is it best to create a table for emails and a table for phone #'s?

[June7]

Why have separate tables? Because might not be data for every employee?
Yes, could be a lot of empty cells.
Not strictly normalized but IMO, it is a balancing act between normalization and ease of data entry/edit. You decide how far to go.

As for duplication of data where the same phone or email associated with multiple people - who remembers when people shared phone numbers? Really, a whole family had the SAME phone number!!!

Personally, I would not use separate tables for phone and email.

[ItsMe]

You can do either or.

There is this thing called Normalization Rules. The rules follow a couple different schools of thought, but most all of the principals are the same. Basically, following Normalization Rules, you strive for the highest normalization form possible. In general, the higher the form the more tables you will have. In general, the higher the form the more difficult it will be to build a Graphical User Interface. One way it is phrased, "Normalize till it hurts, de-normalize till it works."

I have built DB's with very complex tables for contacts and also DB's with few tables to manage contacts. With fewer tables, you will likely duplicate data and violate a few rules of normalization. With fewer tables it may be more difficult to enforce referential integrity when a user is required to type duplicate values in more than one field. As the user types, they may not type the exact same characters in the different fields where the exact same characters are required. The alternative would be more tables and automation.

With more or many tables, code written by you or tools intrinsic to Access will cascade updates or append records to the various child tables. Some complex relations will require VBA code to maintain referential integrity and manage constraints.

I suggest you try both, without getting too elaborate. Create some queries and try to update and append records. Then, read this post again.

[AdamLeeZ]

Question for you on the second entity video above....in the video he says the doctor to patient is a one to many relationship....what happens when the doctor who is your doctor isn't in and then another dr takes over, wouldn't that be a different relationship? or am I missing something? from what i'm understanding wouldn't it be many (optional) doctors to many patients? I fear i'm missing something...

[June7]

I don't think you are missing something, in fact that is a very astute observation. Yes, must identify data entities and relationships. If you want a db that allows each patient to associate with only 1 doctor, then that is a relationship you will have to enforce. If you want the many-to-many relationship then build accordingly. Perhaps the junction table would be tblVisits with fields to select doctor and patient IDs.

[ItsMe]

...what happens when the doctor who is your doctor isn't in and then another dr takes over...

These are the types of things that can be a big gotcha. As mentioned by June, it is an astute observation that recognizes a possibility of a many to many relationship.

I usually recognize these after applying business rules. It is a grey line that separates the database from the application. I try to fit everything into a one to many relationship. After I get the tables on paper, I will test the theory by building the tables in the Access Designer and building queries in the Access designer. I will do my best to avoid building any forms and use my imagination to consider various business rules as I enter data directly into query and table objects. The idea is to try and break the design I have on paper before I build too much in the Access designer.

Eventually, I will start to build out forms and try to break my database design. It is likely the design of the tables will need to be changed more than once. There is this thing called Big Design Up Front. If you BDUF by getting everything perfect on paper. You will always be on paper. If you do not design up front, you will be constantly tearing your application apart as the table structure is updated.

Back to the many to many. You may be able to draw a parallel if you consider a contact that is a Group or contact like a Conference Room. For instance, a computer may have its own Email address and might be treated like a contact. How your business interacts with these entities will determine the relations. You need to look at it from two different perspectives. You need to consider how operations interacts with the entities, both forwards and backwards. If you look at a conference room from the Conference Room's perspective, it is first person from the Conference Room. If you look at it from an individual's perspective, an individual visiting the conference room, it is first person from the individual. If the business rules mandate the application manage both perspectives, you may need a many to many.

If I am one doctor, I care about my many patients. If I am one patient, I care about my many doctors. Different operations or aspects of a business might consider both the Doctor and the Patient.

A junction table can be used to manage many to many relationships. I use VBA code to append records to junction tables.

[AdamLeeZ]

Thanks a lot for the info. I'm slowly digesting it... I appreciate the time you've all taken to provide your support. I hope you have patience with me. I'm intelligent but this is still all so new to me. I've managed databases for 12 years but i've never built them from scratch...it's like learning a new language. But I love the challenge...this should be fun. I'm signing out for the weekend and off to remodel my house but I look forward to many more posts and the continuing conversation.

[AdamLeeZ]

I am completing my ERD and have some questions. I'm unsure of how to map out our UPC Table...we have multiple types of UPC setups. One for a singular item, one for the same item in a carton, one for the same item in a pack, and one for the same item in a master case. So for one single Finished Product we can have up to 4 UPC codes assigned to it depending on the pack type. How would I mock up a table in this scenario? Should I create a table for UPC code types as opposed to creating 4 separate tables for each type?

[ItsMe]

For inventory control, I will have a list of items. So, a table that lists all of the different items. Depending on the business rules, I might include an attribute like case count as Text and have the User type the case count value at the time the item is appended. If the pieces within a case have attributes that are unique to the pieces, I will use a separate table.

So I am not imagining why you would want a table for UPC code types. If a pallet can be ordered and it has an order/inventory number, that number will be added to tblUnits as a Business Key. The cases within will go in another table and the pieces within each case will go in yet another.

That would be a scenario for inventory and manufacturing is different.

[AdamLeeZ]

I think maybe I should explain first what the database I'm building is for...maybe then it will make more sense.

This database will most likely ONLY be used for our R&D Project Managers and Food Technicians to track their projects and all the tasks that go along with those projects. Master Data is a small team I am part of that manages material creation and that info gets relayed across the organization. It would be much easier to input the information we create into the database when we get assigned a request to create new material codes once the projects have been approved. This would also make it easier for the Project managers to get the data as opposed to having to send an email with the data. I want to be able to show the UPC codes for each packaging type per finished product, so when the project manager pulls up the Project, they see each level of packaging assigned a UPC code when necessary. This database is not going to track order inventory nor is manufacturing going to use this database. We currently use an excel file to track projects....a bit archaic for a 5 billion dollar organization.... this is a temporary solution before we start sourcing for a Product Lifecycle management system. I'm not trying to build something for the company that massive...I would need more manpower and hours.

[ItsMe]

... each level of packaging assigned a UPC code when necessary...

I would probably view this from a perspective of manufacturing. When doing so you need a list of raw materials, junction tables for assembly, and another list for the final product. If the final product has multiple levels (child tables), you would produce the Inventory solution I provided above for the final assembly.

If you need to have a departmental application that manages the various levels of a similar product. You are probably looking at multiple tables. My view of the issue and my explanation of multiple tables to manage inventory seem similar to your opinion regarding

...create a table for UPC code types as opposed to...

I would include the UPC and quantity as attributes to the child tables.

... If the pieces within a case have attributes that are unique to the pieces, I will use a separate table.

I hope that helps a little. Store the PK values of parent tables in FK's of child tables. Also, I will usually include ALL parent tables' PK values in relative child tables. So a sub-sub-table would have multiple FK columns to describe each of the relative records in each of the parent tables. I break the Normalization rules for the ease of creating queries.

In the end, you will have multiple tables. Try to imagine them as a single table. This way, you might imagine how this group of tables is a list of possible Items. It would be highly unlikely this list would receive UPDATES. On occasion, records would be appended.