我在制作好的关系数据库有点糊涂了。 我使用phpMyAdmin来创建数据库。 我有以下四个表。 不要担心这个事实的地方,价格都是可选的,他们只是有。
- 人(强制性)
- 项目(强制性)
- 广场(可选)
- 价格(可选)
项目是主表。 它永远有个人联系在一起。 *我知道你不加入MySQL中的表。 如果我想的表连接在一起,我可以使用组合键(使用每个表的ID),然而,这是链接表最正确的方法是什么? 这也意味着项目将有5个IDS包括它自己。 这一切都导致空值(显然是一个很大的不,不,我可以理解),因为如果地方和价格是可选的,在一个条目的项目表中没有使用我将有一个空值存在。 请帮忙!
提前致谢。 我希望这是有道理的。
NULL values
It also means item will have 5 ids including its own. This all cause null values (apparently a big no no, which I can understand) because if place and price are optional and are not used on one entry to the items table I will have a null value there
Personally I think this is one situation where NULL
values are perfect, and I certainly wouldn't have any doubts about putting this into my database design.
One way I've seen others achieve the same thing without NULL
values is to create a record in the optional tables (place and price in your example) with an ID of 0 that signifies there's no related record - but this just makes 10 times more work for the developer of the application to filter these records out - it's FAR easier to do a join and if you don't get any records back, there are no related records in the optional table.
Just remember to do a LEFT
or RIGHT OUTER
join if you want to return Item
s regardless of whether they have a Place
or Price
associated (you'll get NULL
values in the optional table columns for Item
s that don't have associated records) and an INNER
join if you only want the Item
s that do have an associated optional record.
Composite Keys
A composite key is a key in a table that's made up of more than one column. If each of your Person
, Item
, Place
and Price
all have an ID (even if it's just an auto-incrementing number) you won't need a composite key - just a primary key column in each table, and a foreign key column in the Item
table for each related table - e.g. item_id
, person_id
, place_id
, price_id
. You state that Item
has its own ID, so you shouldn't need a composite key - just a primary key on the item_id
column.