Complicated Database Manipulation

问题:

I draw a very simple database design for my ASP.NET application and with a little help here and there I managed to implement my design...but I want to insert data to this tables and retrieve from them.

A very important function in my application will need all the information from all the 7 tables for a certain record, and sometimes I'll need to make my application accept all kinds of related data and place each piece of data in the right table

My questions are :

1. is it normal to have such big design ? is it even big for a real application ?
2. if I want to add a new kid, I'll need to fill all the table with related required information in all the tables, I'll have a big form with some inputs to accept the data, but how will I insert them in to my Database ?
3. If I want to select all the kids, what's the select statement based on this design going to be ?

PS: between every two tables having (M:M) relationship, consider a third table (that will have two primary keys of each of the 2 tables in the relation).

I'm familiar with JOINs and everything, but never applied on such a big design...HELP! Thanks in advance!

回答1:

Sorry to frighten you, but that's a very small database design.

When I first started to need to do database designs for web applications, I found a crutch. Fortunately, the crutch I used back then is now available in a free tool, NORMA, which works with either Visual Studio 2008 or 2005 (2010 support is coming).

I made a quick guess about your design and got the following ORM model:

This led to a database with ten tables, including the junction tables necessary for many to many relationships.

The NORMA tool can generate DDL from the model for several different databases, and can also create XML Schemas and LINQ to SQL objects.

---

BTW, here's an ER diagram of the generated database:

---

I don't see why you would need all of this in a single resultset, but here's a query which I think might return it all. I haven't tested this with data:

SELECT k.KidId, k.Name, k.FavoriteToy, 
    s.SponsorId, s.Name, 
    st.SponsorShipTypeCode, st.Description AS SponsortshipTypeDescription,
    fw.FieldOfWorkCode, fw.Description AS FieldOfWorkDescription,
    Skill.SkillCode, Skill.Description AS SkillDescription,
    Parent.ParentId, Parent.Name AS ParentName
FROM Kid k
LEFT OUTER JOIN KidHasParent ON KidHasParent.KidId = k.KidId
INNER JOIN Parent ON Parent.ParentId = KidHasParent.ParentId
LEFT OUTER JOIN KidHasSkill ON KidHasSkill.KidId = k.KidId
INNER JOIN Skill ON Skill.SkillCode = KidHasSkill.SkillCode
LEFT OUTER JOIN Sponsor s ON s.SponsorId = k.SponsorId
INNER JOIN SponsorIsOfSponsorShipType Sst ON Sst.SponsorId = s.SponsorId
INNER JOIN SponsorShipType st ON st.SponsorShipTypeCode = sst.SponsorShipTypeCode
LEFT OUTER JOIN SponsorParticipatesInFieldOfWork sfw ON sfw.SponsorId = s.SponsorId
INNER JOIN FieldOfWork fw ON fw.FieldOfWorkCode = sfw.FieldOfWorkCode

I created this query by looking at the ORM diagram to know which paths are optional (LEFT JOIN), and then by using a tool (ApexSQL Edit) which uses the foreign keys to help me build joins. This took under ten minutes.

回答2:

1. That is not a big design, but rather a smallish one. It will likely grow as you further develop your application.

2. You will put information into your tables by collecting the various values from the user interface and preparing a series of INSERT statements to be sent to the database engine. Each INSERT will add a row to a table. If you are using database-generated IDs then you will probably have to retrieve them between INSERTs and use the value as part of the subsequent INSERT statement.

3. You'll get the data back using one or more SELECT statements that get data from a single table or JOIN data from several different tables. If you find that these JOINed SELECT statements constitute important, often used concepts you may choose to encapsulate them in some VIEWs.

回答3:

To try to answer your questions:

1. This is a pretty small design. My current project at work has 150 tables, arranged not unlike what you have.

2. Ideally to enter a new kid you'll have to only enter data in the Kid table. The easiest way to visualize this from a GUI point of view is to have one GUI table per database table. That way you can specify the Parents, Sponsors, Skills, Fields of Work, etc., on their own forms.

3. You'll have to ask yourself how much information is appropriate to supply for each kid based on the need. An interface that showed a list of kids with their parents, favorite toy, sponsors, skills, etc., will become way too crowded. Depending on your application, you'll probably want to be more specific on what data you show. For example, you might want to list all the information for kids when you specify them by name. If you need to query for kids who are sponsored by plumbers (sponsors' fields of work), for example, you probably don't care what their favorite toy is.