Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Database Schema

Posted on 2014-01-30
7
Medium Priority
?
271 Views
Last Modified: 2014-01-31
I've worked with MS SQL on and off for a number of years and can do most of the basic things without any formal training.  I'm currently working with a rather large database with 50+ tables and views .....

The problem I always struggle with is how the tables associate with each other etc; and have to try things through trial and error .....

Many of the tables have fields in them and a predetermined set of values set in those fields but I'm not always sure what they mean.

I have the schema provided by the vendor for each of the tables and fields but I'm thinking their has to be more.

Again I'm not an expert by any means just thinking I'm missing something and thought I should ask.

Appreciate any help you might be able to give ......  

Thanks ...

Joel
0
Comment
Question by:jtbrown1111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
7 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 39823257
Hi,

It depends if your vendor has defined any foreign key constraints.

What a foreign key constraint is saying is that the values in this column in this table must match those from the key column in a foreign table. This means that your table to the foreign table has a many to one relationship. Now that might actually end up as a one to one, but this table is the many ...

Can you post the schema?

HTH
  David
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 39823263
WHat is your question?

Without knowledge of the database, the tables and the schema you can't tell how tables are related.

Assuming the database deisgner wsan't being cute you should be able to determine some of the relationships between tables by the table names.

For instance if you have a table called Customer and a table called Addresses there is probably a relationship between the 2 since a customer generally has one or more addresses.  However an address may have several customers so without further information it is impossible to determine how the tables are related.

With just those 2 tables you need to know
Do all customers have at least 1 address?
Can a cutomer have more than 1 address?
Can an address have more than 1 customer?
Do all addresses have at least one customer?

Are addresses other than customer addresses in the address table, for instance supplier, employee, etc.

Do you have a database diagram or design document?
Does the database itself have table relationships stored?

Without any other documents I would start with the assumption that the table names and field/column names are meaningful and can be used to help determine the relationships.

For instance in our 2 tables above Customers and Addresses.
If they both have fields named CustomerId then this probably the linking field.

If the address table has a field like Addressee ID then that is probaly the link but it may indicate the addresses aren't just for customers but also for other tables like employees, suppliers, store locations, etc.

Try running some simple queries like

SELECT Customer.Name, Address.Street
FROM Customer LEFT OUTER JOIN Address
ON Customer.CustomerID = Address.AddresseeID

ALso try

SELECT Customer.Name, Address.Street
FROM Customer INNER JOIN Address
ON Customer.CustomerID = Address.AddresseeID

If they return the same rows then you probably have a relationship where all customers have at least one address.

Do you see a customer repeated?
If so, a customer can have more than one address

If you are missing some customers in the INNER JOIN results then a customer does not have to have an address.  You should see them in the first example

Reverse the JOIN so you can see if there are addresses without customers


Are there other tables that sound related to each other
Orders, Order Details

Think about what the data represents.
Generally you can determine a great deal of general information.  It is for sales, an address book, company data

Is this a custom database or is it based on one of the more popular commercial databases like JD Edwards, SalesForce, QuickBooks.
Many of these have support websites that can help with the database schema.

mlmcc
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39823264
Hi,

I suggest that mlmcc's first query example should have read

SELECT Customer.Name, Address.Street
FROM Customer LEFT OUTER JOIN Address
ON Customer.CustomerID = Address.CustomerID

Regards
  David
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 101

Expert Comment

by:mlmcc
ID: 39823279
Probably.  If you notice I used ADDRESSEE and not ADDRESS.

That was catering to the Address table being used for addresses of all types and not just customers.

mlmcc
0
 

Author Comment

by:jtbrown1111
ID: 39824381
mlmcc ,  Would you be interested in chatting on the phone about this issue ?  Probably easier ..

Joel
0
 

Author Comment

by:jtbrown1111
ID: 39824527
Thanks everyone for your help .....   I beleive I have my questions answered ....  Much appreciated ...

Joel
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question