?
Solved

SQL Database Schema

Posted on 2014-01-30
7
Medium Priority
?
270 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

741 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