• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

SQL Database Schema

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
jtbrown1111
Asked:
jtbrown1111
  • 2
  • 2
  • 2
1 Solution
 
David ToddSenior DBACommented:
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
 
mlmccCommented:
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
 
David ToddSenior DBACommented:
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
mlmccCommented:
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
 
jtbrown1111Author Commented:
mlmcc ,  Would you be interested in chatting on the phone about this issue ?  Probably easier ..

Joel
0
 
jtbrown1111Author Commented:
Thanks everyone for your help .....   I beleive I have my questions answered ....  Much appreciated ...

Joel
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now