Solved

SQL Database Schema

Posted on 2014-01-30
7
261 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
  • 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 100

Accepted Solution

by:
mlmcc earned 500 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 100

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now